Recursive CTEs in SQL Server

A recursive CTE refers to ITSELF. It has two parts joined by UNION ALL:

WITH Cte AS
(
    SELECT ...            -- 1) ANCHOR member : starting rows
    UNION ALL
    SELECT ... FROM Cte   -- 2) RECURSIVE member : uses previous level
    WHERE <stop condition>
)
SELECT * FROM Cte OPTION (MAXRECURSION 100);   -- default limit = 100 levels

Used for: org charts, category trees, bill of materials, number/date series.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: BillOfMaterials (a laptop is made from parts, parts from sub-parts)

DROP TABLE IF EXISTS dbo.BillOfMaterials;
DROP TABLE IF EXISTS dbo.Parts;

CREATE TABLE dbo.Parts
(
    PartID   INT PRIMARY KEY,
    PartName VARCHAR(50)   NOT NULL,
    PartCost DECIMAL(10,2) NOT NULL     -- own cost (labour / raw)
);

INSERT INTO dbo.Parts (PartID, PartName, PartCost) VALUES
(1,  'Laptop',          2000),
(2,  'Motherboard',     1500),
(3,  'Display Panel',   6000),
(4,  'Battery Pack',    1200),
(5,  'CPU',            15000),
(6,  'RAM Module',      3000),
(7,  'SSD',             4000),
(8,  'Battery Cell',     300),
(9,  'Screws',             5),
(10, 'LCD Glass',       2500),
(11, 'Backlight',        800);

CREATE TABLE dbo.BillOfMaterials
(
    ParentPartID INT NOT NULL REFERENCES dbo.Parts(PartID),
    ChildPartID  INT NOT NULL REFERENCES dbo.Parts(PartID),
    Quantity     INT NOT NULL,
    PRIMARY KEY (ParentPartID, ChildPartID)
);

INSERT INTO dbo.BillOfMaterials (ParentPartID, ChildPartID, Quantity) VALUES
(1, 2, 1),  (1, 3, 1),  (1, 4, 1), (1, 9, 20),   -- Laptop
(2, 5, 1),  (2, 6, 2),  (2, 7, 1), (2, 9, 8),    -- Motherboard
(3, 10, 1), (3, 11, 1), (3, 9, 4),               -- Display Panel
(4, 8, 6);                                       -- Battery Pack
GO

(11 rows affected)
(12 rows affected)

Part A: Number and Date Series

Numbers 1 to 10

WITH Numbers AS
(
    SELECT 1 AS n                 -- anchor
    UNION ALL
    SELECT n + 1 FROM Numbers     -- recursive
    WHERE n < 10                  -- stop condition (VERY important)
)
SELECT n FROM Numbers;

All dates of January 2025 (a calendar)

WITH Calendar AS
(
    SELECT CAST('2025-01-01' AS DATE) AS CalDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalDate) FROM Calendar
    WHERE CalDate < '2025-01-31'
)
SELECT CalDate, DATENAME(WEEKDAY, CalDate) AS DayName
FROM Calendar;

Fill gaps: daily sales for Jan 2025 including days with Zero sales

WITH Calendar AS
(
    SELECT CAST('2025-01-01' AS DATE) AS CalDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalDate) FROM Calendar WHERE CalDate < '2025-01-31'
)
SELECT c.CalDate, ISNULL(SUM(s.TotalAmount), 0) AS DailySales
FROM Calendar c
LEFT JOIN dbo.Sales s ON s.SaleDate = c.CalDate
GROUP BY c.CalDate
ORDER BY c.CalDate;

More than 100 levels needs Maxrecursion (0 = no limit, be careful!)

WITH Numbers AS
(
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < 365
)
SELECT COUNT(*) AS TotalNumbers FROM Numbers
OPTION (MAXRECURSION 400);

Part B: Employee Hierarchy (org Chart)

Whole org chart with level and path, starting from the Ceo

WITH OrgChart AS
(
    SELECT EmployeeID, FirstName, JobTitle, ManagerID,
           0 AS Level,
           CAST(FirstName AS VARCHAR(500)) AS HierarchyPath
    FROM dbo.Employees
    WHERE ManagerID IS NULL                       -- anchor : the CEO

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.JobTitle, e.ManagerID,
           oc.Level + 1,
           CAST(oc.HierarchyPath + ' > ' + e.FirstName AS VARCHAR(500))
    FROM dbo.Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID   -- children of previous level
)
SELECT REPLICATE('    ', Level) + FirstName AS OrgTree, JobTitle, Level, HierarchyPath
FROM OrgChart
ORDER BY HierarchyPath;

Everyone who reports (directly or indirectly) to Vikram (cto, EmployeeID = 3)

WITH Team AS
(
    SELECT EmployeeID, FirstName, ManagerID, 1 AS Depth
    FROM dbo.Employees WHERE ManagerID = 3
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, t.Depth + 1
    FROM dbo.Employees e JOIN Team t ON e.ManagerID = t.EmployeeID
)
SELECT * FROM Team ORDER BY Depth, EmployeeID;

Walk Up the chain: managers above Divya (EmployeeID = 12) up to the Ceo

WITH Chain AS
(
    SELECT EmployeeID, FirstName, ManagerID, 0 AS StepsUp
    FROM dbo.Employees WHERE EmployeeID = 12
    UNION ALL
    SELECT m.EmployeeID, m.FirstName, m.ManagerID, c.StepsUp + 1
    FROM dbo.Employees m JOIN Chain c ON m.EmployeeID = c.ManagerID
)
SELECT * FROM Chain ORDER BY StepsUp;

Total salary cost under each manager (including the whole sub-tree)

WITH SubTree AS
(
    SELECT EmployeeID AS RootID, EmployeeID, Salary FROM dbo.Employees
    UNION ALL
    SELECT st.RootID, e.EmployeeID, e.Salary
    FROM dbo.Employees e JOIN SubTree st ON e.ManagerID = st.EmployeeID
)
SELECT e.FirstName, e.JobTitle, COUNT(*) - 1 AS TeamSize, SUM(st.Salary) AS TotalCostIncludingSelf
FROM SubTree st
JOIN dbo.Employees e ON e.EmployeeID = st.RootID
GROUP BY e.FirstName, e.JobTitle
HAVING COUNT(*) > 1
ORDER BY TotalCostIncludingSelf DESC;

Part C: Category Tree

Full category path like "Electronics > Computers > Laptops"

WITH CatTree AS
(
    SELECT CategoryID, CategoryName, ParentCategoryID,
           CAST(CategoryName AS VARCHAR(300)) AS FullPath, 1 AS Lvl
    FROM dbo.Categories WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID,
           CAST(t.FullPath + ' > ' + c.CategoryName AS VARCHAR(300)), t.Lvl + 1
    FROM dbo.Categories c JOIN CatTree t ON c.ParentCategoryID = t.CategoryID
)
SELECT CategoryID, FullPath, Lvl FROM CatTree ORDER BY FullPath;

Revenue rolled up to the TOP level category (Electronics, Furniture, ...)

WITH CatRoot AS
(
    SELECT CategoryID, CategoryID AS RootCategoryID FROM dbo.Categories WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, r.RootCategoryID
    FROM dbo.Categories c JOIN CatRoot r ON c.ParentCategoryID = r.CategoryID
)
SELECT root.CategoryName AS TopCategory, SUM(oi.LineTotal) AS Revenue
FROM dbo.OrderItems oi
JOIN dbo.Products   p    ON p.ProductID = oi.ProductID
JOIN CatRoot        cr   ON cr.CategoryID = p.CategoryID
JOIN dbo.Categories root ON root.CategoryID = cr.RootCategoryID
GROUP BY root.CategoryName
ORDER BY Revenue DESC;

Part D: Bill of Materials (explode parts and total cost)

All parts needed to build One laptop with total quantity

WITH Explode AS
(
    SELECT b.ChildPartID AS PartID, b.Quantity AS TotalQty, 1 AS Lvl
    FROM dbo.BillOfMaterials b WHERE b.ParentPartID = 1
    UNION ALL
    SELECT b.ChildPartID, e.TotalQty * b.Quantity, e.Lvl + 1
    FROM dbo.BillOfMaterials b JOIN Explode e ON b.ParentPartID = e.PartID
)
SELECT p.PartName, e.Lvl, SUM(e.TotalQty) AS QtyNeeded,
       SUM(e.TotalQty * p.PartCost) AS Cost
FROM Explode e
JOIN dbo.Parts p ON p.PartID = e.PartID
GROUP BY p.PartName, e.Lvl
ORDER BY e.Lvl, p.PartName;

Total cost to build one laptop

WITH Explode AS
(
    SELECT 1 AS PartID, 1 AS TotalQty
    UNION ALL
    SELECT b.ChildPartID, e.TotalQty * b.Quantity
    FROM dbo.BillOfMaterials b JOIN Explode e ON b.ParentPartID = e.PartID
)
SELECT SUM(e.TotalQty * p.PartCost) AS TotalLaptopCost
FROM Explode e JOIN dbo.Parts p ON p.PartID = e.PartID;

Common Mistakes

• Missing stop condition -> error "maximum recursion 100 has been exhausted".
• Data cycle (A manages B, B manages A) -> infinite loop. Protect with a Level limit or by checking the path: WHERE oc.HierarchyPath NOT LIKE '%' + e.FirstName + '%'
• Anchor and recursive column types must match exactly -> use CAST.

PRACTICE QUESTIONS 1. Generate all month-start dates between 2024-01-01 and 2025-12-01. 2. Show the number of levels below each manager (max depth of their sub-tree). 3. List all sub-categories under 'Electronics' at any depth. 4. Find how many 'Screws' are needed for one laptop. 5. Generate the first 15 Fibonacci numbers using a recursive CTE.