SQL Server Recursive Common Table Expressions (Recursive CTE) | syntaxhelp.com

SQL Server Recursive Common Table Expressions (Recursive CTE)

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

Generating an Organizational Hierarchy using a Recursive CTE

DECLARE @emp TABLE (EmpID INT, Title VARCHAR(15), ParentID INT)
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 1, 'CEO', NULL
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 2, 'PM 1', 1
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 3, 'CFO', 1
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 4, 'Tech Lead', 2
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 5, 'Accountant', 3
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 6, 'Developer', 4
INSERT INTO @emp(EmpID, Title, ParentID) SELECT 7, 'DBA', 4

;WITH cte AS (
    SELECT 0 AS Lvl, EmpID, Title
    FROM @emp WHERE ParentID IS NULL
    UNION ALL
    SELECT p.lvl + 1, c.EmpID, c.Title 
    FROM @emp c
    INNER JOIN cte p ON p.EmpID = c.ParentID
)
SELECT 
    SPACE(lvl * 4) + Title AS Hierarchy
FROM cte
/*
Hierarchy
--------------------------------------------------
CEO
    PM 1
    CFO
        Accountant
        Tech Lead
            Developer
            DBA
*/

Generating a Date Series using a Recursive CTE

DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = '2009-01-01', @endDate = '2009-01-10'
;WITH cte AS (
    SELECT @startDate AS dt
    UNION ALL
    SELECT dt + 1 FROM cte
    WHERE dt + 1 <= @endDate
)
SELECT * FROM cte
/*
dt
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
*/

Share |

Cant find the page you are looking for?
Help us to improve by adding the content that you are looking for.
Leave a feedback
We look forward to hear your comments and feedback.

11610 reads