SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Circular iteration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rischfre
Starting Member

Spain
15 Posts

Posted - 05/22/2013 :  06:20:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 05/22/2013 :  06:37:13  Show Profile  Reply with Quote
Refer this link
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

--
Chandu
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 05/22/2013 :  06:50:12  Show Profile  Visit webfred's Homepage  Reply with Quote
--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



Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/22/2013 :  06:51:54  Show Profile  Reply with Quote
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
Go to Top of Page

rischfre
Starting Member

Spain
15 Posts

Posted - 05/22/2013 :  09:31:02  Show Profile  Reply with Quote
Thank you very much thants workign very well
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/23/2013 :  00:55:37  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 05/23/2013 :  03:02:53  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000