Recursive CTEs for Hierarchical Data: Examples

Organization Chart with Levels and Paths

advanced

Build complete org chart showing reporting structure, management levels, and full reporting paths from CEO to each employee

WITH RECURSIVE org_chart AS (

-- Base case: Find CEO (no manager)

SELECT

id,

name,

manager_id,

title,

1 as level,

name as path,

CAST(name AS VARCHAR(1000)) as sort_path

FROM employees

WHERE manager_id IS NULL

UNION ALL

-- Recursive case: Find direct reports

SELECT

e.id,

e.name,

e.manager_id,

e.title,

oc.level + 1,

oc.path || ' → ' || e.name,

oc.sort_path || '|' || e.name

FROM employees e

JOIN org_chart oc ON e.manager_id = oc.id

WHERE oc.level < 10 -- Prevent infinite recursion

)

SELECT

level,

REPEAT(' ', level - 1) || name as indented_name,

title,

path

FROM org_chart

ORDER BY sort_path;

level | indented_name | title | path

------+-------------------------+--------------------+----------------------------------

1 | Sarah Chen | CEO | Sarah Chen

2 | Michael Rodriguez | VP Engineering | Sarah Chen → Michael Rodriguez