Recursive Queries using Common Table Expression

Problem Statement Part 1: What is the best approach to find out Employee-Manager hierarchy using a given sample table of employees.

On the first thought, best is to use self join.

Problem Statement Part 2: But in case the level of hierarchy is not available and it has to be identified

Solution: “Recursive Query”.

Here is an example:

[code language=”sql”]
–Create a sample table
CREATE TABLE dbo.Employees
(
EmployeeID INT PRIMARY KEY NOT NULL,
Name VARCHAR(80) NOT NULL,
ManagerID u NULL,
);
— Insert values in the table.
INSERT INTO dbo.Employees
VALUES
(1, ‘Steve’,NULL)
,(2, ‘Maria’,1)
,(4, ‘Bob’,2)
,(5, ‘Ajay’,4)
,(6, ‘Martin’,4)
,(8, ‘Rupendra’,2)
,(9, ‘Neeraj’,8)
,(16, ‘Laxman’, 2)
,(23, ‘Ram’,16);
[/code]

Below is the recursive query:

[code language=”sql”]
WITH CTE AS
(
SELECT EmployeeID, Name, ManagerID,Name AS ManagerName, 0 AS Levels
FROM dbo.Employees
WHERE ManagerID IS NULL

UNION ALL

SELECT E.EmployeeID, E.Name,E.ManagerID,emp.Name AS ManagerName, emp.Levels+1 AS Levels
FROM dbo.Employees E
JOIN CTE emp ON emp.EmployeeID= E.ManagerID
–WHERE

)

SELECT * FROM CTE
[/code]
The above query will also show the level upto which hierarchy goes.