Round #669

Write a recursive CTE to walk an employee-manager hierarchy from any starting employee.

Certainly! To walk an employee-manager hierarchy using a Recursive Common Table Expression (CTE) in SQL, you can use the following query. This example assumes that you have a table named `Employees` with columns `EmployeeID`, `ManagerID`, and `EmployeeName`. Here's how you can write the recursive CTE: ```sql WITH RECURSIVE EmployeeHierarchy AS ( -- Anchor member: Select the starting employee(s) SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level -- Initial level (root node) FROM Employees WHERE EmployeeID = @StartingEmployeeID -- Replace with the actual starting employee ID UNION ALL -- Recursive member: Select subordinates of each employee in the hierarchy SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.Level + 1 AS Level FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT EmployeeID, ManagerID, EmployeeName, Level FROM EmployeeHierarchy ORDER BY Level, EmployeeID; ``` ### Explanation: 1. **Anchor Member**: - This part of the CTE selects the starting employee(s) from the `Employees` table. - The `Level` column is initialized to 0 for the root node (starting employee). 2. **Recursive Member**: - This part joins the `Employees` table with the `EmployeeHierarchy` CTE on the condition that the `ManagerID` of an employee matches the `EmployeeID` in the CTE. - The `Level` column is incremented by 1 for each recursive step, indicating the depth in the hierarchy. 3. **Final Selection**: - The final `SELECT` statement retrieves all columns from the `EmployeeHierarchy` CTE and orders the results by `Level` and `EmployeeID`. ### Usage: - Replace `@StartingEmployeeID` with the actual ID of the employee you want to start the hierarchy from. This query will return a list of employees starting from the specified employee, along with their manager IDs and levels in the hierarchy.

To implement a recursive Common Table Expression (CTE) to walk an employee-manager hierarchy from any starting employee, we can use SQL Server's `WITH RECURSIVE` syntax. This approach is suitable for hierarchical data structures like an employee-manager relationship. Here's the complete code: ```sql -- Create sample tables and insert some data CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name NVARCHAR(100), ManagerID INT, FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID) ); INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3); -- Recursive CTE to walk the employee-manager hierarchy WITH EmployeeHierarchy AS ( -- Anchor member: select the starting employee SELECT EmployeeID, Name, ManagerID, CAST(EmployeeID AS NVARCHAR(MAX)) AS Path, 1 AS Level FROM Employees WHERE EmployeeID = @StartingEmployeeID UNION ALL -- Recursive member: join with the anchor to get subordinates SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Path + ' -> ' + CAST(e.EmployeeID AS NVARCHAR(MAX)), eh.Level + 1 FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) -- Select the results from the CTE SELECT EmployeeID, Name, ManagerID, Path, Level FROM EmployeeHierarchy ORDER BY Path; ``` ### Explanation: 1. **Table Creation and Data Insertion**: - We create a table `Employees` with columns for `EmployeeID`, `Name`, and `ManagerID`. - Sample data is inserted into the table to represent an employee-manager hierarchy. 2. **Recursive CTE (`EmployeeHierarchy`)**: - **Anchor Member**: This part of the CTE selects the starting employee based on a parameter `@StartingEmployeeID`. It initializes the path with the starting employee's ID and sets the level to 1. - **Recursive Member**: This part joins the `Employees` table with itself to find subordinates. It appends each subordinate's ID to the path and increments the level. 3. **Selecting Results**: - The final query selects all columns from the CTE, ordering by the path to display the hierarchy in a readable format. ### Usage: To use this CTE, you would typically pass the `@StartingEmployeeID` parameter when executing the query. For example: ```sql DECLARE @StartingEmployeeID INT = 1; -- Starting from Alice WITH EmployeeHierarchy AS ( SELECT EmployeeID, Name, ManagerID, CAST(EmployeeID AS NVARCHAR(MAX)) AS Path, 1 AS Level FROM Employees WHERE EmployeeID = @StartingEmployeeID UNION ALL SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Path + ' -> ' + CAST(e.EmployeeID AS NVARCHAR(MAX)), eh.Level + 1 FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT EmployeeID, Name, ManagerID, Path, Level FROM EmployeeHierarchy ORDER BY Path; ``` This query will return the entire hierarchy starting from Alice, showing each employee and their path in the hierarchy.