<thecfguy blog="sharing thought">

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.