Bits of .NET

🌳 Key Takeaways: Recursive CTEs query tree structures, org charts, and hierarchical data without complex code.

Querying hierarchical data is hard. Recursive CTEs make it easy.

📁 Sample Data: Employee Hierarchy

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    ManagerId INT NULL,
    Department NVARCHAR(50)
);

INSERT INTO Employees VALUES
(1, 'CEO', NULL, 'Executive'),
(2, 'CTO', 1, 'Tech'),
(3, 'CFO', 1, 'Finance'),
(4, 'Lead Developer', 2, 'Tech'),
(5, 'Developer', 4, 'Tech'),
(6, 'Junior Developer', 4, 'Tech'),
(7, 'Accountant', 3, 'Finance'),
(8, 'Intern', 6, 'Tech');

🚀 Basic Recursive CTE

-- ✅ Get entire org tree under CTO (ID 2)
WITH OrgTree AS (
    -- Anchor: Start with CTO
    SELECT Id, Name, ManagerId, 0 AS Level,
           CAST(Name AS NVARCHAR(MAX)) AS Path
    FROM Employees
    WHERE Id = 2
    
    UNION ALL
    
    -- Recursive: Find direct reports
    SELECT e.Id, e.Name, e.ManagerId, ot.Level + 1,
           CAST(ot.Path + ' > ' + e.Name AS NVARCHAR(MAX))
    FROM Employees e
    INNER JOIN OrgTree ot ON e.ManagerId = ot.Id
)
SELECT 
    REPLICATE('  ', Level) + Name AS IndentedName,
    Level,
    Path
FROM OrgTree
ORDER BY Path;

💡 Advanced: Depth and Breadth

-- ✅ Find management chain for a user
WITH ManagementChain AS (
    -- Anchor: Start with user
    SELECT Id, Name, ManagerId, 0 AS Level,
           CAST(Name AS NVARCHAR(MAX)) AS Path
    FROM Employees
    WHERE Id = 5  -- Developer
    
    UNION ALL
    
    -- Recursive: Find manager
    SELECT e.Id, e.Name, e.ManagerId, mc.Level + 1,
           CAST(e.Name + ' > ' + mc.Path AS NVARCHAR(MAX))
    FROM Employees e
    INNER JOIN ManagementChain mc ON e.Id = mc.ManagerId
)
SELECT Name, Level, Path
FROM ManagementChain
ORDER BY Level;

-- ✅ Find all subordinates (with depth)
WITH Subordinates AS (
    SELECT Id, Name, ManagerId, 0 AS Depth
    FROM Employees
    WHERE Id = 2  -- CTO
    
    UNION ALL
    
    SELECT e.Id, e.Name, e.ManagerId, s.Depth + 1
    FROM Employees e
    INNER JOIN Subordinates s ON e.ManagerId = s.Id
)
SELECT 
    REPLICATE('  ', Depth) + Name AS Employee,
    Depth
FROM Subordinates
ORDER BY Depth, Name;

💡 Pro Tip: Aggregate Hierarchical Data

-- ✅ Count subordinates per manager
WITH OrgCount AS (
    SELECT Id, ManagerId, 1 AS Count
    FROM Employees
    
    UNION ALL
    
    SELECT e.Id, e.ManagerId, oc.Count
    FROM Employees e
    INNER JOIN OrgCount oc ON e.ManagerId = oc.Id
)
SELECT 
    e.Name AS Manager,
    COUNT(oc.Count) - 1 AS SubordinateCount
FROM Employees e
LEFT JOIN OrgCount oc ON e.Id = oc.Id
GROUP BY e.Name
ORDER BY SubordinateCount DESC;