Let’s create a complete example using a recursive CTE with sample data, including table creation, data insertion, and the recursive query itself.

Step 1: Create the Sample Table

First, we'll create a simple employees table to hold our employee data.

CREATE TABLE employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
ManagerID INT,
FOREIGN KEY (ManagerID) REFERENCES employees(EmployeeID)
);

Step 2: Insert Sample Data

Now, we will insert some sample data into the employees table.

INSERT INTO employees (EmployeeID, Name, ManagerID) VALUES
(1, 'Alice', NULL), -- Alice is the top-level manager
(2, 'Bob', 1), -- Bob reports to Alice
(3, 'Carol', 1), -- Carol reports to Alice
(4, 'Dave', 2), -- Dave reports to Bob
(5, 'Eve', 2), -- Eve reports to Bob
(6, 'Frank', 3), -- Frank reports to Carol
(7, 'Grace', 3); -- Grace reports to Carol

Step 3: Recursive CTE to Retrieve Employee Hierarchy

Now we will write the recursive CTE to retrieve the hierarchy of employees, starting from Alice (the top-level manager).

WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: Select the top-level manager (Alice)
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM employees
WHERE ManagerID IS NULL

UNION ALL

-- Recursive member: Select employees reporting to the current level's employees
SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID

)
-- Final selection: Get the entire hierarchy
SELECT EmployeeID, Name, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeID;

Explanation of the CTE:

1. Anchor Member:

Selects the employee who has no manager (ManagerID IS NULL), which is Alice in this case.

It also includes a Level column to indicate the hierarchy level (0 for Alice).

1. Recursive Member:

Selects employees whose ManagerID matches the EmployeeID from the previous result set.

It increments the Level by 1 to indicate how deep in the hierarchy the employee is.

1. Final Selection:

Retrieves the results from the EmployeeHierarchy CTE, ordering by level and employee ID.

Output

When you run the above CTE, the output will look like this:

Summary

Level 0: Alice (the top-level manager)

Level 1: Bob and Carol (direct reports to Alice)

Level 2: Dave and Eve (reports to Bob) and Frank and Grace (reports to Carol)

This example demonstrates how to use a recursive CTE to traverse a hierarchical structure in SQL.