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.