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