Sql Server: CTE Recursive Query to Get Employee Manager Hierarchy with Level

Introduction: In this article I have explained How to get parent child hierarchical relationship with levels using CTE (Common Table Expression) recursive query in sql.

In previous articles i explained How to Drop or truncate parent table by dropping all foreign key constraints and CTE to remove duplicate records from table and Autogenerate auto incremented unique alphanumeric id or number in sql server and  Multiple queries to get all dates between two dates and Without primary key column update first or last n records in table

Description: While working with database we often store parent and child id in same table. For example Category and sub category Id, Employee and his manager id etc. Here in this article I am taking an example where employees and their manager are stored in same table. And suppose it is required to get employee and his manager and hierarchical level of employee in organization. CTE is very useful in such case because of its recursive capability.

Implementation: Let’s understand how it works.

Let’s create table using following script

CREATE TABLE  tbEmployee

(

EmployeeId                INT PRIMARY KEY,

EmployeeName         VARCHAR(50),

ManagerId                 INT

)

Enter some dummy data in table using following insert query:

INSERT tbEmployee 

VALUES 

(25,'Salman',NULL),

(26,'Ranbeer', 25),

(27,'Hrithik',25),

(28,'Aamir',27),

(29,'Shahid',28),

(30,'Sidharth', NULL),

(31,'Varun', 30),

(32,'Kabeer', 30),

(33,'Raj', 29); 

Show table data

SELECT *FROM tbEmployee

Result:

EmployeeId	EmployeeName	ManagerId
25	Salman	NULL
26	Ranbeer	25
27	Hrithik	25
28	Aamir	27
29	Shahid	28
30	Sidharth	NULL
31	Varun	30
32	Kabeer	30
33	Raj	29

CTE recursive query to get employee and manager relationship hierarchy with level.

;WITH EMP_CTE AS

(

SELECT EmployeeId, EmployeeName, ManagerId, CAST('' AS VARCHAR(50)) ManagerName, 0 AS EmployeeLevel FROM tbEmployee WHERE ManagerId IS NULL

UNION ALL

SELECT T.EmployeeId,T.EmployeeName, T.ManagerId,CAST(C.EmployeeName AS VARCHAR(50)) ManagerName, EmployeeLevel + 1 AS EmployeeLevel FROM tbEmployee AS T

INNER JOIN EMP_CTE  AS C ON C.EmployeeId=T.ManagerId

)

SELECT * FROM EMP_CTE

Result:

EmloyeeId	EmployeeName	ManagerId	ManagerName	EmployeeLevel
25	Salman	NULL		0
30	Sidharth	NULL		0
31	Varun	30	Sidharth	1
32	Kabeer	30	Sidharth	1
26	Ranbeer	25	Salman	1
27	Hrithik	25	Salman	1
28	Aamir	27	Hrithik	2
29	Shahid	28	Aamir	3
33	Raj	29	Shahid	4

Explanation: The base record for the CTE is obtained by the first select query above UNION ALL. It gets all EmployeeId which don’t have ManagerId ie. NULL value. This means they are the top most employees of the organization so their Employee Level is set to 0.

Second select query below UNION ALL is executed recursively to get results and it will continue until it returns no rows. E.g. Result will have EmployeeIds which have ManagerId (i.e, EmployeeId of the first result).  This is obtained by joining our CTE result with tbEmployee table on columns EmployeeId of CTE with ManagerId of table tbEmployee. 

This process is recursive and will continue till there is no ManagerId who doesn’t have EmployeeId.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post