Round #801

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; 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 query = @" WITH RecursiveCTE AS ( SELECT EmployeeId, EmployeeName, ManagerId, 0 AS Level FROM Employees WHERE EmployeeId = @startingEmployeeId UNION ALL SELECT e.EmployeeId, e.EmployeeName, e.ManagerId, Level + 1 FROM Employees e INNER JOIN RecursiveCTE m ON e.ManagerId = m.EmployeeId ) SELECT EmployeeId, EmployeeName, ManagerId, Level FROM RecursiveCTE ORDER BY Level "; using (var command = new SqlCommand(query, connection)) { command.Parameters.AddWithValue("@startingEmployeeId", startingEmployeeId); using (var adapter = new SqlDataAdapter(command)) { var dataTable = new DataTable(); adapter.Fill(dataTable); return dataTable; } } } } } ``` This C# code uses a recursive Common Table Expression (CTE) to walk an employee-manager hierarchy from any starting employee. The `GetEmployeeHierarchy` method takes a `startingEmployeeId` as input and returns a `DataTable` containing the employee hierarchy. The recursive CTE starts with the specified employee and then recursively joins the `Employees` table to itself, traversing the hierarchy. The `Level` column indicates the distance from the starting employee. Note: This code assumes the existence of an `Employees` table with columns `EmployeeId`, `EmployeeName`, and `ManagerId`. You may need to adjust the table and column names to match your actual database schema. This implementation genuinely requires more text to provide a complete explanation and example usage.