Mastering Recursive CTEs in SQL for Organizational Hierarchy Reporting

Why Recursive CTEs Matter

When I started working with corporate data, I quickly realized that many problems are not simple one‑level queries but nested structures. An employee org chart, product category tree, or bill‑of‑materials explosion all share the same pattern: each row may reference another row as its parent. The classic way to handle this in SQL is a **recursive common table expression (CTE)**. It lets you define a base query and then repeatedly join back to the CTE itself until no more rows are produced. The result is a flat result set that still preserves the parent‑child relationship and depth information—exactly what reporting tools need.

Real‑World Problem: Flattening a Hierarchy

Our HR application stored employee data in a single table:

• EmployeeID – unique identifier
• ManagerID – references another EmployeeID (NULL for the CEO)
• FullName, Salary, Department

The business wanted a report that listed every employee, their direct manager’s name, the depth in the organization (CEO = 0, direct reports = 1, …), and a running total of salaries for each department across all subordinates. A simple join would only give us the immediate manager; we needed the entire chain. A recursive CTE solved this cleanly and gave us a single result set we could feed into our BI dashboard.

Building the Solution: A Recursive CTE Example

Below is a production‑ready script that you can drop into any SQL Server (or PostgreSQL, MySQL 8.0+ – the syntax is ANSI‑SQL) database. It returns each employee with manager name, depth, and department salary totals.

/* ------------------------------------------------------------
   Recursive CTE to flatten an employee hierarchy.
   Returns:
     EmployeeID, FullName, ManagerID, ManagerName,
     Depth, Department, Salary, DeptSalaryTotal
   ------------------------------------------------------------ */
WITH EmployeeHierarchy AS (
    /* 1. Anchor member – start with the CEO(s) (ManagerID IS NULL) */
    SELECT
        EmployeeID,
        FullName,
        ManagerID,
        CAST(FullName AS VARCHAR(200)) AS ManagerName,   -- self‑reference for root
        CAST(0 AS INT) AS Depth,
        Department,
        Salary
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    /* 2. Recursive member – join each employee to its parent */
    SELECT
        e.EmployeeID,
        e.FullName,
        e.ManagerID,
        eh.FullName AS ManagerName,
        eh.Depth + 1 AS Depth,
        e.Department,
        e.Salary
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT
    eh.EmployeeID,
    eh.FullName AS EmployeeName,
    eh.ManagerID,
    eh.ManagerName,
    eh.Depth,
    eh.Department,
    eh.Salary,
    dept_total.DeptSalaryTotal
FROM EmployeeHierarchy eh
/* Compute department salary totals using a window function */
CROSS JOIN (
    SELECT
        Department,
        SUM(Salary) AS DeptSalaryTotal
    FROM EmployeeHierarchy
    GROUP BY Department
) AS dept_total
ORDER BY eh.Department, eh.Depth, eh.EmployeeID;

Comments are placed inline to explain each section. The script is safe to run on large tables because the recursion stops as soon as there are no more parent rows—an inherent guard against infinite loops.

Using the Result for Reporting

After executing the query, you get a flat list that looks like this (sample rows):

The DeptSalaryTotal column shows the sum of all salaries under that department, which is handy for quick compliance checks. You can now bind this result directly to a pivot table, export to Excel, or feed it into a data warehouse.

Performance Tips and Gotchas

• Set MAXRECURSION if you suspect deep hierarchies (SQL Server). Use OPTION (MAXRECURSION 0) to lift the default 100 limit.
• Indexes on ManagerID dramatically speed up the recursive join. Without it, the engine performs a scan each iteration.
• Recursive CTEs are **set‑based**, not cursor‑like. They return a single result set, which makes them easier to compose with other CTEs or derived tables.
• Avoid mixing SELECT * with heavy computed columns inside the recursion; each iteration re‑evaluates the whole query.
• If you need to preserve the original hierarchy order (top‑down vs. bottom‑up), add ORDER BY Depth in the anchor or recursive part.

Pro tip: When you need to drill down further (e.g., display the entire path from CEO to each employee), concatenate the names in the recursion using STUFF((SELECT ... FOR XML PATH('')).value('.','NVARCHAR(MAX)'),1,1,'') AS Path. This can be a single column that looks like Alice CEO → Bob Mgr → Carol Rep.

When Not to Use Recursion

Recursion shines for hierarchical data, but it isn’t a silver bullet. If your dataset contains millions of levels, the performance penalty can be severe. In those cases, consider materializing the hierarchy in a separate table (adjacency list with pre‑computed path) or using a **nested set model** if reads dominate writes.

Also, be mindful that some databases (older MySQL, SQLite) do not support recursive CTEs at all. When portability is a requirement, you might need to fall back to application‑level recursion or a temporary hierarchy table.

Wrap‑Up

Recursive CTEs are a deceptively simple trick that can transform tangled parent‑child data into a clean, reportable flat structure. By anchoring on the root nodes and repeatedly joining back to the CTE, you let the engine handle the depth automatically. The example above demonstrates a realistic HR scenario, but the pattern applies to product categories, file system folders, or any tree‑shaped data you encounter.

Next time you face a “drill‑down” requirement, remember that a well‑crafted recursive CTE often eliminates the need for complex procedural loops or multiple temporary queries. It keeps the logic declarative, the code maintainable, and the performance predictable—exactly the kind of tool a senior developer reaches for when building production‑grade solutions.