Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL - Recursive CTE's
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-12-09








Example 1: Recursive CTE - Generate a List - Numbers:


;WITH CTENumbers AS
(
SELECT num = 1 --Anchor Member

UNION ALL

SELECT num + 1 --Recursive Member
FROM CTENumbers
WHERE num + 1 <= 1000
)

SELECT num AS 'List of Numbers'
FROM CTENumbers
OPTION (MAXRECURSION 1000)



Example 2: Recursive CTE - Generate a List - Days of Year:

Can be used with other logic to solve a problem that requires a list of dates


DECLARE @varstartdate VARCHAR(100) = '2016-01-01'; --First Day of Year
DECLARE @varenddate VARCHAR(100) = '2016-12-31'; --Last Day of Year

;WITH CTEdates ([Date]) as
(

Select CONVERT(DATE, @varstartdate) as [Date] --Anchor Member - Start at first day of Year

Union All

Select dateadd(day, 1, [Date]) --Recursive Member - Add 1 Day to the Anchor Member
from CTEdates
where [Date] < CONVERT(DATE, @varenddate) --Stop at Last Day of Year

)

Select [Date] AS 'Days of Year' FROM
CTEdates
OPTION (MAXRECURSION 1000)




Example 3: Recursive CTE - Generate a List - Days of Current Month:

Can be used with other logic to solve a problem that requires a list of dates


WITH CTEdates ([Date]) as
(

Select DATEFROMPARTS(YEAR(Getdate()), MONTH(GETDATE()),1) as [Date]

union all

Select dateadd(day, 1, [Date])
from CTEdates
where [Date] < EOMONTH(GETDATE())

)

Select [Date]
from CTEdates
option (MAXRECURSION 100)




Example 4: Recursive CTE - Navigate a Hierarchy like Employee Manager


Data Prep:

CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Title nvarchar(100) NOT NULL,
DeptID int NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

-- Populate the table with values.
INSERT INTO dbo.Employees VALUES
(1, N'Bill', N'Gates', N'President',1000,NULL)
,(2, N'James', N'Smith', N'CEO',2000,1)
,(3, N'Bob', N'Taylor', N'VP Finance',2000,1)
,(4, N'Chris', N'Penton', N'VP Sales',2000,1)
,(5, N'Blair', N'Connor', N'UK Sales Manager',3000,4)
,(6, N'Jean', N'Tanner', N'UK Salesperson',3000,5)
,(7, N'Michelle', N'Armstrong', N'UK Salesperson',3000,5)
,(8, N'Cliff', N'Hendor', N'USA Sales Manager',4000,4)
,(9, N'Jon', N'Sittler', N'USA Salesperson',4000,8)
,(10, N'Peggy', N'Pearson', N'USA Salesperson',4000,8)
,(11, N'Nick', N'Plont', N'USA Salesperson',4000,8)



Recursive CTE:

;WITH CTEEmployeeManager (EmployeeID, Title, FirstName, LastName, ManagerID, DeptID, Level)
AS
(
--Anchor member definition (Get the Top Level - ManagerID is null - i.e. President)
SELECT e.EmployeeID, e.Title, e.FirstName, e.LastName, e.ManagerID, e.DeptID, 0 AS Level --Set Level 0
FROM dbo.Employees AS e
WHERE ManagerID IS NULL

UNION ALL

--Recursive member definition
SELECT e.EmployeeID, e.Title, e.FirstName, e.LastName, e.ManagerID, e.DeptID, Level + 1 --Increment Level (when the ManagerID changes the Level changes)
FROM dbo.Employees AS e
INNER JOIN CTEEmployeeManager AS cte
ON e.ManagerID = cte.EmployeeID
)

--Stmt that executes the CTE
SELECT EmployeeID, Title, FirstName, LastName, ManagerID, DeptID, Level
FROM CTEEmployeeManager
ORDER BY Level, DeptID ASC
GO