Parent-Child Recursive Query using CTE

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

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 :

Finally

Drop Table #TempEmpMgr