Recursive Sample for Employee Structure (T-SQL)



Create Table Test(empid  int primary key,  empName varchar(50),  hr_id int);
Insert into Test  Values
(16,'John',12),(15,'Joe',13),(13,'Chad',12),(12,'David',11)
,(11,'Adam',null),(6,'Jean',5),(5,'Paige',4)
,(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;

go

Drop Table Test;


 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s