sql-recursive-cte-org-chart

Answer

The recursive CTE uses two parts:

1. Base case — employees with no manager (manager_id IS NULL) are the root nodes at depth 0 with their name as the path.
2. Recursive case — joins each employee to their manager in the CTE, incrementing depth and appending the employee name to the path.

WITH RECURSIVE org_chart AS (
    -- Base case: root employees (no manager)
    SELECT
        id,
        name,
        manager_id,
        0 AS depth,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with a manager
    SELECT
        e.id,
        e.name,
        e.manager_id,
        oc.depth + 1,
        oc.path || ' -> ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth, path
FROM org_chart
ORDER BY depth, id;

For the sample data: | id | name | depth | path | |----|------------|-------|------------------------------------------------| | 1 | CEO | 0 | CEO | | 2 | VP Eng | 1 | CEO -> VP Eng | | 3 | VP Sales | 1 | CEO -> VP Sales | | 4 | Eng Mgr | 2 | CEO -> VP Eng -> Eng Mgr | | 7 | Sales Lead | 2 | CEO -> VP Sales -> Sales Lead | | 5 | Sr Eng | 3 | CEO -> VP Eng -> Eng Mgr -> Sr Eng | | 6 | Jr Eng | 3 | CEO -> VP Eng -> Eng Mgr -> Jr Eng | | 8 | Sales Rep | 3 | CEO -> VP Sales -> Sales Lead -> Sales Rep |