Retrieving Employee Hierarchies Using Recursive CTE

Create a Recursive CTE for Hierarchical Data

Write a SQL query to retrieve all employees and their managers using a recursive CTE.

Solution:

-- Retrieve all employees and their managers using a recursive CTE.
WITH EmployeeHierarchyCTE AS (
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        CAST(Name AS NVARCHAR(MAX)) AS HierarchyPath
    FROM Employees
    WHERE ManagerID IS NULL -- Base case: top-level employees (no manager).
    UNION ALL
    SELECT 
        E.EmployeeID,
        E.Name,
        E.ManagerID,
        CAST(EH.HierarchyPath + ' -> ' + E.Name AS NVARCHAR(MAX))
    FROM Employees E
    INNER JOIN EmployeeHierarchyCTE EH ON E.ManagerID = EH.EmployeeID
)
SELECT * FROM EmployeeHierarchyCTE;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use a recursive CTE to traverse hierarchical data, such as employee-manager relationships.

2. Key Components :

1. WITH EmployeeHierarchyCTE : Defines the recursive CTE.
2. Base Case : Selects top-level employees (those without a manager).
3. Recursive Step : Joins employees with their managers to build the hierarchy.
4. CAST : Ensures the hierarchy path can grow dynamically.

3. Why use Recursive CTEs for Hierarchies? :

1. Recursive CTEs are ideal for traversing hierarchical or tree-like structures in relational databases.

4. Real-World Application :

1. For example, in organizational charts, you might use this query to display employee hierarchies.

Additional Notes:

• Be cautious with recursion depth to avoid performance issues or infinite loops.
• Use this exercise to teach how to handle hierarchical data structures.

For more Practice: Solve these Related Problems:

• Write a SQL query to retrieve all employees and their direct subordinates using a recursive CTE.
• Write a SQL query to display the full hierarchy path of each employee in an organization.
• Write a SQL query to count the number of subordinates for each manager in a hierarchical structure.
• Write a SQL query to find all employees who report directly or indirectly to a specific manager.

Go to:

• Master Advanced SQL Exercises Home ↩
• SQL Exercises Home ↩

PREV : Use LAG and LEAD Functions.
NEXT : Use FIRST_VALUE and LAST_VALUE Functions.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.