March 26, 2010 at 12:47 am
Experts:
I have the following table with sample data...This has employeeIDs and Their reporting manager id...
When one id is passed then i need to find employees and their managers traversing down to lowest level...
Curently am using cursor to get the result....i want to avoid that....
Create table Recurse (ID int,Empid varchar(10),ReportToID varchar(10))
Insert into Recurse values(1, 'Emp1','Emp100')
Insert into Recurse values(2, 'Emp2','Emp1')
Insert into Recurse values(3, 'Emp3','Emp1')
Insert into Recurse values(4, 'Emp4','Emp1')
Insert into Recurse values(5, 'Emp6','Emp4')
Insert into Recurse values(6, 'Emp8','Emp10')
Insert into Recurse values(7, 'Emp9','Emp10')
Insert into Recurse values(8, 'Emp11','Emp111')
--Expected results
-- when we pass ID as 1, result should be like
IDEMPIDReporttoid
1Emp1Emp100
2Emp2Emp1
3Emp3Emp1
4Emp4Emp1
5Emp6Emp4
TIA...
March 26, 2010 at 2:14 am
Here u go buddy!
;WITH HierarchyCTE (ID, EmpID , ReportingManager)
AS
(
SELECT EH.ID, EH.EmpID , EH.ReportToID
FROM Recurse EH
WHERE EH.ReportToID = 'Emp100' -- Here u must specify the top most employee Id, i.e., ReporttoID
UNION ALL
SELECT EH.ID, EH.EmpID , EH.ReportToID
FROM Recurse EH
INNER JOIN HierarchyCTE CTE ON EH.ReportToID = CTE.EmpID
)
SELECT * FROM HierarchyCTE ORDER BY ID, EmpID , ReportingManager
Please inform us if it works 🙂
March 26, 2010 at 2:22 am
U can pass the ID column as well there...
;WITH HierarchyCTE (ID, EmpID , ReportingManager)
AS
(
SELECT EH.ID, EH.EmpID , EH.ReportToID
FROM Recurse EH
WHERE EH.ID = 1 -- Here u specify your ID
UNION ALL
SELECT EH.ID, EH.EmpID , EH.ReportToID
FROM Recurse EH
INNER JOIN HierarchyCTE CTE ON EH.ReportToID = CTE.EmpID
)
SELECT * FROM HierarchyCTE ORDER BY ID, EmpID , ReportingManager
the code in the post above this and this code both works fine..
kindly confirm us if both work for u 🙂
March 26, 2010 at 4:07 am
It Rocks...Cool dude...thanks a lot...
March 26, 2010 at 4:46 am
Welcome dude!
March 26, 2010 at 4:57 am
A very similar example (and some useful others) can be found in Books Online:
WITH common_table_expression (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 26, 2010 at 9:09 am
Paul White NZ (3/26/2010)
A very similar example
hello Paul, i learnt CTE from BOL, so thot that i will frame the code similar to the one in BOL :-D... my tribute to MS :kiss:
March 26, 2010 at 10:39 am
COldCoffee (3/26/2010)
hello Paul, i learnt CTE from BOL...
We all did 😉
No worries - just thought 'Jus' might appreciate the link, for some more details and examples.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 12:16 am
Yes paul! 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply