More About Common Table Expressions in SQL Server 2005 - Displaying the depth of recursion when using recursive CTE: listing levels
(Page 3 of 5 )
Let us start with another example, which gives the level of every employee.
SELECT
EmployeeID, ManagerID,0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID is Null
The above example simply displays EmployeeID, ManagerID and EmployeeLevel as "0," for those who are not working under any manager (in this case, it gives us only one employee, who is the CEO).
Now let us work with a recursive CTE:
WITH EmpCTE
AS
(
SELECT
EmployeeID, ManagerID,0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID is Null
UNION ALL
SELECT
b.EmployeeID, b.ManagerID,EmployeeLevel+1
FROM HumanResources.Employee b
INNER JOIN EmpCTE AS a ON a.EmployeeID = b.ManagerID
)
SELECT EmployeeID,ManagerID,EmployeeLevel
FROM EmpCTE
Order By EmployeeLevel
The sample output for the above CTE would be something like the following:
EmployeeID ManagerID EmployeeLevel
---------- --------- -------------
109 NULL 0
6 109 1
12 109 1
.
.
268 273 2
284 273 2
.
.
.
47 30 3
70 30 3
82 30 3
.
.
.
31 210 4
45 210 4
56 210 4
68 210 4
81 210 4
I shall explain the above script in the next section.
Next: Displaying the depth of recursion when using recursive CTE: explanation >>
More MS SQL Server Articles
More By Jagadish Chaterjee
