A Recursive CTE is a CTE that references itself repeatedly or recursively to generate subsets of data. It performs multiple iterations till it generates the complete set of data.
The video tutorial on our YouTube channel can be found here-
In its simplest form, recursive CTE has the below structure.
The Anchor Query produces the base results.
The recursive query iterates to produce more data till the exit criteria (termination check) is met.
- Sequences
In the example, we are generating a sequence of numbers. This is the first use case of Recursive CTEs - to generate sequences.
WITH RecursiveCTE AS
(
SELECT 1 AS Cnt -- Anchor Query
UNION ALL -- Separator
SELECT Cnt + 1 FROM RecursiveCTE -- Recursive Query
WHERE Cnt < 10 -- Termination Check
)
SELECT * FROM RecursiveCTE
The base query starts the counter by assigning a value of 1 to Cnt. The recursive Query keeps on incrementing the counter by 1 till a series of 10 numbers is produced.
Example 2 - Series of dates within a date range
Using similar logic, we can also generate a series of dates within a range using the below query.
declare @startdate date = '2022-01-01';
declare @enddate date = '2022-01-31';
With Dates as
(Select @startdate as OrderDate
UNION ALL
Select DATEADD(dd,1,OrderDate)
from Dates
WHERE DATEADD(dd,1,OrderDate) <= @enddate)
Select Dates.OrderDate
from Dates
Here we define variables to hold values for the start and end dates of the range. The DateAdd function increments the date by one day in each iteration till the end date is reached.
Example 3 - Sequence of alphabets A-Z
We can also generate a sequence of alphabets. The ascii values of alphabets need to be incremented till the entire series is generated. The Char() function is used to convert the ascii value back to the alphabet.
We start with 65 as the ascii value of alphabet 'A' is 65.
With Alphabets as
(select char(65) alphabet
union all
select char(ascii(alphabet) + 1)
from Alphabets
where alphabet <> 'Z')
Select * from Alphabets
Example 4 - Extract each character from a string in a separate row
Extending the above logic to generate a sequence of numbers, to extract each alphabet from a string in a separate row.
Knowstar --> K
n
o
w
s
t
a
r
The first part of the query generates a series of numbers for each character of the string. The termination check on the length of the string makes sure that we have the same count of numbers as the characters in the string.
The Substring function extracts one character at a time from the corresponding position generated by the recursive CTE.
declare @var varchar(50) = 'Knowstar';
With Recursive_CTE as
( Select @var as var , 1 as start
UNION ALL
Select var, start + 1
from Recursive_CTE
WHERE start + 1 <= len(@var)
)
Select
substring(@var, start, 1)
from Recursive_CTE
- Hierarchies
The next major use case of Recursive CTEs is to extract the multi-level hierarchies. For ex - Employee Manager Hierarchy.
Example 5 - Employee Manager Hierarchy
A manager can have multiple employees under him who in turn might be managers to further employees at a lower level.
With EmpMgrCTE
as
(SELECT EmployeeID, EMployeeName, ManagerID, 0 as EmployeeLevel
from dbo.Employee
WHERE ManagerID is NULL
UNION ALL
SELECT emp.EmployeeID, emp.EMployeeName, emp.ManagerID, mgr.EmployeeLevel + 1 as EmployeeLevel
from dbo.Employee emp
INNER JOIN EmpMgrCTE as mgr
ON emp.ManagerID = mgr.EmployeeID
)
Select * from EmpMgrCTE
order by EmployeeLevel;
Select * from dbo.employee;
The anchor query extracts the top most manager as Level 1.
The recursive query executes a self join to extract the manager employee relationship and executes till all the levels have been extracted.
Example 6 - Bill of Materials
Another similar example is Bill of Materials.
Here a component is made of subcomponents which in turn are made of further subcomponents.
The example is based on the PARTLIST table as below -
CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER);
Insert into PartList VALUES
( '00', '01' , 5),
('00', '05' , 3),
('01', '02' , 2),
('01', '03' , 3),
('01', '04' , 4),
('01', '06' , 3),
('02', '05' , 7),
('02', '06' , 6),
('03', '07' , 6),
('04', '08' , 10),
('04', '09' , 11),
('05', '10' , 10),
('05', '11' , 10),
('06', '12' , 10),
('06', '13' , 10),
('07', '14' , 8),
('07', '12' , 8);
The query below extracts all levels of subcomponents a part is made up of.
With PartList_CTE as
(
Select Part, SubPart, Quantity , 1 as Level
from PARTLIST
Where Part = '00'
UNION ALL
Select Child.Part, Child.SubPart, Child.Quantity , Level + 1
FROM PartList_CTE Parent
INNER JOIN PartList Child
ON Parent.SubPart = Child.Part
)
Select *from PartList_CTE
Another example of hierarchy that can be resolves using Recursive CTE is the family tree.