Common Table Expressions (CTEs)

13.1 Basic CTE

WITH CustomerSales AS
(
    SELECT o.CustomerID, SUM(oi.LineTotal) AS Total
    FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    GROUP BY o.CustomerID
)
SELECT c.FirstName, cs.Total
FROM CustomerSales cs JOIN dbo.Customers c ON c.CustomerID = cs.CustomerID
WHERE cs.Total > 5000;

13.2 Multiple CTEs

WITH A AS (SELECT ...), B AS (SELECT ... FROM A)
SELECT * FROM B;

13.3 Recursive CTE (Hierarchies)

WITH OrgChart AS
(
    SELECT EmployeeID, FullName, ManagerID, 0 AS Lvl,
           CAST(FullName AS NVARCHAR(4000)) AS Path
    FROM dbo.Employees WHERE ManagerID IS NULL              -- anchor
    UNION ALL
    SELECT e.EmployeeID, e.FullName, e.ManagerID, oc.Lvl + 1,
           CAST(oc.Path + N' > ' + e.FullName AS NVARCHAR(4000))
    FROM dbo.Employees e JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID   -- recursive
)
SELECT * FROM OrgChart ORDER BY Path
OPTION (MAXRECURSION 100);   -- default 100; 0 = unlimited

13.4 Practical CTE Patterns

A CTE is not materialized. It's expanded inline each time it is referenced. For reuse, consider a temp table.

-- Number / date generator
WITH Dates AS (SELECT CAST('2025-01-01' AS DATE) AS d
               UNION ALL SELECT DATEADD(DAY, 1, d) FROM Dates WHERE d < '2025-12-31')
SELECT d FROM Dates OPTION (MAXRECURSION 400);

-- Simpler in 2022+:
SELECT value FROM GENERATE_SERIES(1, 100);
SELECT DATEADD(DAY, value, '2025-01-01') FROM GENERATE_SERIES(0, 364);

-- Delete duplicates (keep one)
WITH Dups AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) AS rn
              FROM dbo.Customers)
DELETE FROM Dups WHERE rn > 1;