Round #584

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

**Recursive CTE for Employee-Manager Hierarchy** ===================================================== The following SQL query uses a recursive Common Table Expression (CTE) to walk an employee-manager hierarchy from any starting employee. ```sql -- Create a sample table for demonstration purposes CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(255), ManagerID INT NULL ); -- Insert sample data INSERT INTO Employees (EmployeeID, Name, ManagerID) VALUES (1, 'John Doe', NULL), (2, 'Jane Smith', 1), (3, 'Bob Johnson', 1), (4, 'Alice Brown', 2), (5, 'Mike Davis', 3), (6, 'Emily Taylor', 4); -- Recursive CTE to walk the employee-manager hierarchy WITH RecursiveEmployees AS ( -- Anchor query: select the starting employee SELECT EmployeeID, Name, ManagerID, 0 AS Level FROM Employees WHERE EmployeeID = 2 -- Start with employee 2 (Jane Smith) UNION ALL -- Recursive query: select the manager of the current employee SELECT e.EmployeeID, e.Name, e.ManagerID, Level + 1 FROM Employees e INNER JOIN RecursiveEmployees m ON e.EmployeeID = m.ManagerID ) SELECT * FROM RecursiveEmployees; ``` **How it Works** --------------- 1. The anchor query selects the starting employee (in this case, employee 2, Jane Smith). 2. The recursive query joins the `Employees` table with the `RecursiveEmployees` CTE, using the `ManagerID` column to link an employee to their manager. 3. The recursive query increments the `Level` column by 1 for each iteration, indicating the distance from the starting employee. 4. The `UNION ALL` operator combines the results of the anchor and recursive queries. **Example Output** ----------------- | EmployeeID | Name | ManagerID | Level | |------------|---------------|-----------|-------| | 2 | Jane Smith | 1 | 0 | | 1 | John Doe | NULL | 1 | This output shows the starting employee (Jane Smith) and their manager (John Doe). You can modify the starting employee ID in the anchor query to walk the hierarchy from a different employee. **Tips and Variations** ---------------------- * To walk the hierarchy in the opposite direction (i.e., from a manager to their direct reports), simply swap the join condition in the recursive query. * To include additional columns from the `Employees` table, add them to the `SELECT` clause in both the anchor and recursive queries. * To limit the depth of the recursion, add a `WHERE` clause to the recursive query, filtering on the `Level` column. For example: `WHERE Level < 3` to stop at a maximum depth of 2 levels.

```csharp using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; public class EmployeeHierarchy { public int EmployeeId { get; set; } public string EmployeeName { get; set; } public int? ManagerId { get; set; } public string ManagerName { get; set; } public int Level { get; set; } } public class EmployeeRepository { private readonly string _connectionString; public EmployeeRepository(string connectionString) { _connectionString = connectionString; } public DataTable GetEmployeeHierarchy(int startingEmployeeId) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var command = new SqlCommand("GetEmployeeHierarchy", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@StartingEmployeeId", startingEmployeeId); var adapter = new SqlDataAdapter(command); var dataTable = new DataTable(); adapter.Fill(dataTable); return dataTable; } } } public class Program { public static void Main() { var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; var repository = new EmployeeRepository(connectionString); var startingEmployeeId = 1; var hierarchy = repository.GetEmployeeHierarchy(startingEmployeeId); foreach (DataRow row in hierarchy.Rows) { Console.WriteLine($"EmployeeId: {row["EmployeeId"]}, EmployeeName: {row["EmployeeName"]}, ManagerId: {row["ManagerId"]}, ManagerName: {row["ManagerName"]}, Level: {row["Level"]}"); } } } ``` To implement the recursive CTE in SQL Server, you would create a stored procedure like this: ```sql CREATE PROCEDURE GetEmployeeHierarchy @StartingEmployeeId INT AS BEGIN WITH RecursiveCTE AS ( SELECT E.EmployeeId, E.EmployeeName, E.ManagerId, M.EmployeeName AS ManagerName, 0 AS Level FROM Employees E LEFT JOIN Employees M ON E.ManagerId = M.EmployeeId WHERE E.EmployeeId = @StartingEmployeeId UNION ALL SELECT E.EmployeeId, E.EmployeeName, E.ManagerId, M.EmployeeName AS ManagerName, R.Level + 1 FROM Employees E LEFT JOIN Employees M ON E.ManagerId = M.EmployeeId INNER JOIN RecursiveCTE R ON E.ManagerId = R.EmployeeId ) SELECT EmployeeId, EmployeeName, ManagerId, ManagerName, Level FROM RecursiveCTE ORDER BY Level; END ``` This stored procedure uses a recursive Common Table Expression (CTE) to walk the employee-manager hierarchy from a given starting employee. The CTE consists of two parts: the anchor query and the recursive query. The anchor query selects the starting employee, and the recursive query selects the employees who report to the employees selected in the previous iteration. The `UNION ALL` operator is used to combine the results of the anchor and recursive queries. The `Level` column keeps track of the level of each employee in the hierarchy. The final result is ordered by the `Level` column to show the hierarchy in a clear and readable format.