The Problem of Hierarchies in SQL

When I first started working with employee data, I kept hitting the same roadblock: how to pull out the entire reporting chain for a given manager. A simple `SELECT` from the `Employees` table gave me only the immediate boss‑subordinate pairs. I needed a list that showed every level of the organization, from the CEO down to the intern, and I wanted to compute aggregates like total salary per manager on the fly. The solution that consistently saved me time and mental energy was the recursive Common Table Expression (CTE).

Why a Recursive CTE?

SQL isn’t built around loops, but it does support recursive queries through the `WITH` clause. A recursive CTE consists of two parts:

  • Anchor member – the initial query that defines the starting point.
  • Recursive member – a `UNION ALL` that references the CTE itself, extending the result set step by step.

Because the anchor is evaluated once and the recursive member runs repeatedly until no new rows appear, we can model tree‑like structures such as organizational charts, bill‑of‑materials, or folder hierarchies without leaving the SQL engine.

A Real‑World Scenario: HR Reporting

Imagine the HR team needs a report that lists each manager, all their direct and indirect reports, the depth of each employee in the hierarchy, and the combined salary of that manager’s entire subtree. The old way was to write a stored procedure that used a cursor or to export data to Excel and manually drill down. That was error‑prone and slow.

With a recursive CTE we can generate the full hierarchy in a single query and then apply window functions or aggregations to enrich the data. The result is a set‑based, declarative solution that scales with the size of the organization.

Building the Hierarchy

Below is a production‑ready snippet I keep in my toolbox. It assumes a table named `Employees` with columns `EmployeeID`, `ManagerID`, `EmployeeName`, and `Salary`. The CTE is named `OrgHierarchy`.

WITH OrgHierarchy AS (
    /* Anchor member – start with the root employee(s) (e.g., CEO). */
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName,
        Salary,
        1 AS Level          -- Level 1 = top of the tree
    FROM Employees
    WHERE ManagerID IS NULL      -- adjust condition to match your root(s)

    UNION ALL

    /* Recursive member – walk down the tree. */
    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        e.Salary,
        oh.Level + 1 AS Level   -- increment level for each generation
    FROM Employees e
    INNER JOIN OrgHierarchy oh
        ON e.ManagerID = oh.EmployeeID
)
SELECT *
FROM OrgHierarchy
ORDER BY Level, EmployeeID;

This query returns rows like:

  • `EmployeeID` 1, `ManagerID` NULL, `EmployeeName` 'Alice', `Salary` 120000, `Level` 1
  • `EmployeeID` 2, `ManagerID` 1, `EmployeeName` 'Bob', `Salary` 80000, `Level` 2
  • `EmployeeID` 5, `ManagerID` 2, `EmployeeName` 'Dave', `Salary` 50000, `Level` 3

Now we have every employee and their distance from the root.

Aggregating by Manager

To get the total salary of each manager’s entire subtree, we can use a second CTE that leverages `SUM()` over the hierarchy. The key is to treat each manager as a “parent” and sum the salaries of all rows whose `ManagerID` chain leads back to that manager. The easiest way is to use a window function with `SUM(Salary) OVER (PARTITION BY ManagerID ORDER BY Level)` and then roll up.

WITH OrgHierarchy AS (
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName,
        Salary,
        1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        e.Salary,
        oh.Level + 1 AS Level
    FROM Employees e
    INNER JOIN OrgHierarchy oh
        ON e.ManagerID = oh.EmployeeID
),
ManagerTotals AS (
    SELECT
        oh.EmployeeID AS ManagerID,
        oh.EmployeeName AS ManagerName,
        SUM(oh.Salary) AS TotalSalaryUnderManager,
        COUNT(*) AS DirectReports,
        MAX(oh.Level) AS MaxLevelUnderManager
    FROM OrgHierarchy oh
    GROUP BY oh.EmployeeID, oh.EmployeeName
)
SELECT *
FROM ManagerTotals
ORDER BY TotalSalaryUnderManager DESC;

The result now includes columns like `ManagerID`, `ManagerName`, `TotalSalaryUnderManager`, `DirectReports`, and `MaxLevelUnderManager`. This single statement replaces a cumbersome cursor‑based aggregation and can be dropped into a dashboard query with minimal tuning.

Tips for Production Use

  • Limit recursion depth – SQL Server caps recursion at 100 levels by default. If your org chart could be deeper, increase the limit with `OPTION (MAXRECURSION 0)`.
  • Index the foreign key (`ManagerID`) to keep the recursive join fast. A non‑clustered index on `ManagerID` is usually sufficient.
  • Avoid SELECT * in the recursive part. Pulling only the columns you need reduces memory pressure, especially for wide tables.
  • Cache the CTE if you reuse it often. Some DB engines allow materializing a CTE with `MATERIALIZED`, but a common pattern is to wrap it in a view that is indexed or refreshed periodically.

Pro tip: When you need to output the hierarchy as a formatted string (e.g., “Alice → Bob → Dave”), combine the recursive CTE with `STRING_AGG` (SQL Server 2017+) inside the same query. This keeps the logic set‑based and avoids extra application code.

Wrap‑Up

Recursive CTEs turn a seemingly impossible “walk the tree” problem into a clean, declarative SQL statement. By mastering the anchor/recursive pattern, you can generate org charts, bill‑of‑materials, or any hierarchical dataset and then apply aggregations, window functions, or even string formatting—all within the database engine. The result is faster, more maintainable code that scales with the data and eliminates the need for procedural loops or external tooling.

Next time you face a hierarchical requirement, reach for a recursive CTE first; you’ll thank yourself a few minutes later when the query runs in a fraction of the time it took with cursors or exports.