Circular iteration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Circular iteration

Author

Topic

rischfre
Starting Member

15 Posts

Posted - 2013-05-22 : 06:20:16

Hi

we describe each Job in a table. This table is almost "circular" as one of the key referes to the same table which represents the responsable of each position.

T_Jobs
id_job the KEY
JobDescription the Description
Pid the KEY of the Boss of this Job

Now I would like to do a SELECT which returns: id_job, jobdescription, jobdescription of his boss, and what i do not know how to managed it : at which level is its job.

Level is defined this way:
the CEO (the only with PID = 0) has level 1
the directors (PID = "ID of CEO") have level 2
the managers (PID = "ID of the Directors) have level 3
....

How do i get this circular value "Level"?

thank you, best regards

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-22 : 06:37:13

Refer this link
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-22 : 06:50:12

[code]--T_Jobs
--id_job the KEY
--JobDescription the Description
--Pid the KEY of the Boss of this Job

--Now I would like to do a SELECT which returns: id_job, jobdescription, jobdescription of his boss, and what i do not know how to managed it : at which level is its job.

--Level is defined this way:
--the CEO (the only with PID = 0) has level 1
--the directors (PID = "ID of CEO") have level 2
--the managers (PID = "ID of the Directors) have level 3
--....

--How do i get this circular value "Level"?

-- testdata
declare @T_Jobs table ( id_job int, Job_Description varchar(255), Pid int )
insert @T_Jobs
select 1, 'CEO', null union all
select 245, 'Director X', 1 union all
select 210, 'Director Y', 1 union all
select 105, 'Manager A', 245 union all
select 420, 'Manager D', 210 union all
select 700, 'Manager S', 245

-- show testdata
select * from @T_Jobs

-- solution
;WITH JobCTE
AS
(

SELECT id_job, Job_Description, convert(varchar(255),'') as Job_Description_of_Boss, 1 AS Job_level
FROM @T_Jobs
WHERE Pid is null

UNION ALL

SELECT T.id_job, T.Job_Description, A.Job_Description as Job_Description_of_Boss, A.Job_level + 1
FROM @T_Jobs T
JOIN JobCTE A
ON T.Pid = A.id_job
)
SELECT *
FROM JobCTE[/code]

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 06:51:54

use recursive cte approach

would be like


;With JobHierarchy
AS
(
--Getting first (root) level
SELECT *,CAST(JobDescription AS varchar(max)) AS JobPath
FROM T_Jobs
WHERE PID = 0

UNION ALL

SELECT t.*, CAST(j.JobPath + '/' + t.JobDescription AS varchar(max)) 
FROM JobHierarchy j
INNER JOIN T_Jobs t
On t.PID = j.id_job
)
SELECT  id_job, JobDescription, JobPath
FROM JobHierarchy
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rischfre
Starting Member

15 Posts

Posted - 2013-05-22 : 09:31:02

Thank you very much thants workign very well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:55:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-05-23 : 03:02:53

welcome

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources