Recursive Sample for Employee Structure (T-SQL)

Create Table Test(empid  int primary key,  empName varchar(50),  hr_id int);
Insert into Test  Values
,(4,'Steven',3),(3,'Dan',2),(2,'Ryan',null),(10,'Ruth', 9)
,(9,'Greg',8),(8, 'Sam',7),(7,'Pane',1),(1,'Brandon', null);

;With mycte AS
(Select empid, empName, Cast('' AS varchar(max)) AS path,  empid As ManagerEmpId,  empid AS Seq
From Test Where hr_id Is Null 
Union All
Select t.empid, t.empName, Cast(t.hr_id AS varchar(max)) + Case When m.path <> '' Then ','+ m.path Else '' End AS path, m.ManagerEmpId, m.Seq + t.empid AS Seq 
From mycte m
Inner Join Test t On t.hr_id = m.empid)

Select  * From mycte
Order By ManagerEmpId, Seq;


Drop Table Test;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s