Parent-Child Recursive Query using CTE

Suppose we have parent child relation in same table and storing data in table as display below.

Image1 

We can see that Employee “C” with EID “4” has Manager “B” with MgrID “3” and EID “3” has MgrID “1” and EID “1” has no manager. So in this way if we traverse all Managers of EID “4” we get sequence like as : 1->3->4.

So if we want to retrieve all managers of managers sequence for all EIDs, we need to implement recursive sql query using CTE as below :

--Creating Temp Table
Create
Table #TempEmpMgr
(

EId
int identity(1,1),
Name
nvarchar(20),
MgrId
int
)

--Inserting Temp Data
Insert
into #TempEmpMgr Values

('Manager1',NULL),
(
'A',1),
(
'B',1),
(
'C',3),
(
'Manager2',NULL),
(
'D',5),
(
'E',5),
(
'F',6),
(
'G',8),
(
'Manager3',NULL),
(
'H',10),
(
'I',11),
(
'J',12) 

Select * from #TempEmpMgr

CTE SQL Query as :

;With MyCTE As
(

Select EId, Name, MgrId,0 as [Level], CAST(EId as varchar(max)) as TrailMgrIDs , CAST(Name as nvarchar(max))  as TrailMgrsName
from
#TempEmpMgr where MgrId is NULL
UNION
ALL
Select
T.EId, T.Name, T.MgrId, [Level]+1, TrailMgrIDs+'->'+CAST(T.EId as varchar(max)), TrailMgrsName+'->'+T.Name
from
#TempEmpMgr T Inner Join MyCTE C on T.MgrId=C.EId
)

Select
* from MyCTE  order by EId

Resultset comes out Select statement over MyCTE as below :

Image2.jpg

Finally

Drop Table #TempEmpMgr

Ebook Download
View all
Learn
View all