How to process hierarchical data in SQL-Server using recursive CTE
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 UNION ALL --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 ORDER BY ManagerID;
The actually recursion process is well explained here.
Hope it is interesting.