Greg the Dev

Greg the Dev

How to process hierarchical data in SQL-Server using recursive CTE

Greg's photo
·Apr 11, 2021·

1 min read

We surely have encountered data which was structure in hierarchical mode. One example is storing employees as managers and their direct reports.

The usage sample are already well explained in Microsoft docs.

However the most important thing to remember is the way the recursion takes place.

You need to compose the Common Table Expression (CTE) from anchor member(s) and recursive member(s), at least one from each type, and they need to be combined with a UNION ALL.

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
    --anchor member
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    --recursive member
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  

The actually recursion process is well explained here.

Hope it is interesting.

Share this