Recursive CTEs

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Follow employee-to-manager links through several levels.

Run the example

WITH Hierarchy AS (
    SELECT EmployeeID, ManagerID, FirstName, 0 AS Depth
    FROM dbo.Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.FirstName, h.Depth + 1
    FROM dbo.Employees AS e
    JOIN Hierarchy AS h ON e.ManagerID = h.EmployeeID
)
SELECT EmployeeID, ManagerID, FirstName, Depth
FROM Hierarchy
ORDER BY Depth, EmployeeID
OPTION (MAXRECURSION 100);

Read it step by step

1. The anchor starts with employees who have no manager.
2. The recursive branch finds direct reports of rows already in Hierarchy.
3. UNION ALL continues by level; MAXRECURSION bounds accidental cycles or runaway recursion.

Check the result

Depth 0 is a root employee; later depths are links away from a root.

Practice

Starting at EmployeeID 1, list that employee and all descendants with their depth.

WITH Reports AS (
    SELECT EmployeeID, ManagerID, 0 AS Depth
    FROM dbo.Employees WHERE EmployeeID = 1
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, r.Depth + 1
    FROM dbo.Employees AS e
    JOIN Reports AS r ON e.ManagerID = r.EmployeeID
)
SELECT EmployeeID, ManagerID, Depth
FROM Reports
ORDER BY Depth, EmployeeID
OPTION (MAXRECURSION 100);