Examples of Recursive CTE in SQL server
Let us consider the following table Organization on which we want to find the Hierarchical relationships between the employees and managers of a respective organization.
Example 1: Find Hierarchical Relationship of All Levels
Query:
WITH RecursiveOrganizationCTE AS
(
SELECT EmployeeID, FirstName, LastName, Department,ManagerID
FROM Organization
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.department,e.ManagerID
FROM Organization e
JOIN RecursiveOrganizationCTE r ON e.ManagerID = r.EmployeeID
)
--Show the records stored inside the CTE we created above
SELECT *
FROM RecursiveOrganizationCTE;
Output:
Explanation: The following query uses a recursive CTE to extract hierarchical structure of a table based on the organizational relationships within the table Organization. The query shows the managers who are also employees within the organization at the top level as they have managerID as NULL , then it shows employees who reports to managers as well as act as managers themselves. At last the employees who are not managers who belongs to the last level of the hierarchical structure is shown in the result set.
Example 2: Simple Recursive CTE to Find Out Days in a Week
In the following example we will see how we can use the Recursive CTE in SQL server to extract all the days in a week easily.
Query:
WITH DaysofWeek(x, WeekDays)
AS (
SELECT 0, DATENAME(DW, 0)
UNION ALL
SELECT x + 1, DATENAME(DW, x + 1)
FROM DaysofWeek WHERE x < 6
)
SELECT WeekDays FROM DaysofWeek;
Output:
Explanation: The following query extracts all the days in a week with the help of Recursive CTE and DATENAME() function. The DATENAME() function returns the name of the weekday based on the weekday number. The anchor member is set as 0 and returns the Monday. The recursive member returns the next day of the week starting from tuesday till Sunday based on the value of x which is incremented by 1 recursively after each call.
Example 3: Simple Recursive CTE to Find Out All the Months in a Year
In the following example we will see how we can use the Recursive CTE in SQL server to extract all the months in a year .
Query:
WITH RecursiveMonths AS (
SELECT
1 AS MonthNumber,
DATENAME(MONTH, CAST('2024-01-01' AS DATE)) AS MonthName
UNION ALL
SELECT
MonthNumber + 1,
DATENAME(MONTH, DATEADD(MONTH, MonthNumber, '2024-01-01'))
FROM RecursiveMonths
WHERE MonthNumber < 12
)
SELECT * FROM RecursiveMonths;
Output:
Explanation: The following query is used to extract all the months in a year with the help of Recursive CTE and DATENAME() function. The DATENAME() function returns the name of the month based on the month number. The anchor member is set as 1 and returns the January month. The recursive member returns the next month of the year starting from February to Decemeber based on the value of monthnumber which is incremented by 1 recursively after each call.
Example 4: Find Relationship Between Data Present in the Different Tables Through Recursive CTE.
In the following example we will see how we can use the Recursive CTE to extract meaningful data from multiple tables. Let us consider two tables CityData and CityRoutes on which we will perform our operations.
After inserting some data into the CityData Table, The Table Looks:
After inserting some data into the CityRoutes Table, The Table Looks:
Query:
WITH Destinations AS (
SELECT
RouteID,
SourceCityID,
DestinationCityID,
CAST(CityName AS VARCHAR(MAX)) AS Route,
Distance
FROM CityRoutes CR
INNER JOIN CityData CD ON CR.SourceCityID = CD.CityID
UNION ALL
SELECT
CR.RouteID,
CR.SourceCityID,
CR.DestinationCityID,
CAST(R.Route + ' -> ' + CD.CityName AS VARCHAR(MAX)),
R.Distance + CR.Distance
FROM Destinations R
INNER JOIN CityRoutes CR ON R.DestinationCityID = CR.SourceCityID
INNER JOIN CityData CD ON CR.DestinationCityID = CD.CityID
)
SELECT Route, Distance
FROM Destinations
WHERE SourceCityID = 3;
Output:
Explanation: The following query uses Recursive CTE as well as multiple JOINS to find out the best route and it’s total distance from the id of the source city provided by the user in the query. The anchor member SELECT selects the routes from the CityRoutes table joining it with the CityData table. The recursive member UNION ALL selects additional routes by joining the CityRoutes table with the CTE itself.
Recursive CTE in SQL Server
CTE which is the abbreviation for Common Table Expression is an SQL Server tool that returns a temporary data set that can be used by another query. In this article, we are going to learn about the Implementation of Recursive CTE in SQL servers. We will understand how recursive common table expressions work step by step and understand their workflow through various examples. Make sure you are familiar with Common table Expressions in SQL before diving into the Recursive CTE’s.
Contact Us