thecfguy

A Unique Developer

Get hierarchical list in SQL Server 2008

SQL Server 2005 comes with magical WITH clause which make getting hierarchical (recursive) data very easily (just within one query). Let me explain with example. Suppose I like to get manager hierarchy from employee table. 

Employee Table:

employeeId  empName                                            managerId
----------- -------------------------------------------------- -----------
1           R1                                                 NULL
2           R2                                                 1
3           R3                                                 1
4           R4                                                 2
5           R5                                                 2
6           R6                                                 3
7           R7                                                 2
8           R8                                                 3
9           R9                                                 4

Let's say I want to get hierarchical manager list for employee R9 (Id 9) and just try below query.

WITH Managers(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM Employees where employeeId = 9
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM Employees e
        INNER JOIN Managers m
        ON e.EmployeeID  = m.ManagerID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM Managers

And here is magical result

ManagerID   EmployeeID  EmployeeLevel
----------- ----------- -------------
4           9           0
2           4           1
1           2           2
NULL        1           3

 With clause provide recursive execution over the table row which gives this magical result. Hope you may like it.