Recursive CTE Example: organization chart with depth

Creating an organization chart with depth using a recursive Common Table Expression (CTE) is a practical way to manage and visualize hierarchical data. Here’s how you can achieve this in SQL:

Table Structure

Let's assume you have an employees table structured as follows:

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

Sample Data

Here’s some sample data for this table:

sql
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

SQL Query to Retrieve Organization Chart with Depth

To construct an organization chart with depth, we’ll use a recursive CTE. The depth indicates the level of each employee in the hierarchy, with the top-level manager having a depth of 1.

sql
WITH RECURSIVE OrgChart AS (
    -- Anchor member: start with the top-level manager
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 AS depth
    FROM 
        employees
    WHERE 
        manager_id IS NULL

    UNION ALL

    -- Recursive member: find employees who report to the current employee
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        oc.depth + 1 AS depth
    FROM 
        employees e
    INNER JOIN 
        OrgChart oc ON e.manager_id = oc.employee_id
)
SELECT 
    employee_id,
    employee_name,
    manager_id,
    depth
FROM 
    OrgChart
ORDER BY 
    depth, employee_id;

Explanation

1. Anchor Member:

   sql
   SELECT 
       employee_id,
       employee_name,
       manager_id,
       1 AS depth
   FROM 
       employees
   WHERE 
       manager_id IS NULL

   This initializes the CTE with the top-level manager(s) who have no manager (i.e., manager_id IS NULL). The depth is set to 1 for these managers.

2. Recursive Member:

   sql
   SELECT 
       e.employee_id,
       e.employee_name,
       e.manager_id,
       oc.depth + 1 AS depth
   FROM 
       employees e
   INNER JOIN 
       OrgChart oc ON e.manager_id = oc.employee_id

   This part of the CTE finds employees who report to the current employees in the CTE and increments their depth by 1.

3. Final Select:

   sql
   SELECT 
       employee_id,
       employee_name,
       manager_id,
       depth
   FROM 
       OrgChart
   ORDER BY 
       depth, employee_id;

   This selects and orders the results by depth and then by employee ID.

Result

The result will be a table listing each employee with their respective depth in the organizational hierarchy:

 employee_id | employee_name| manager_id | depth
-------------|--------------|------------|-------
           1 | Alice        | NULL       |     1
           2 | Bob          | 1          |     2
           3 | Charlie      | 1          |     2
           4 | David        | 2          |     3
           5 | Eve          | 2          |     3
           6 | Frank        | 3          |     3

In this example:

• Alice is at the top level (depth 1).
• Bob and Charlie report to Alice, so they are at depth 2.
• David, Eve, and Frank report to Bob and Charlie, so they are at depth 3.

This query effectively constructs the organizational chart, showing each employee’s position in the hierarchy along with their depth.