SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com .

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.

This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

Reference : Pinal Dave (https://blog.sqlauthority.com), Special thanks to Jr. Developers at my organization who helped me to prepare example.

Quest

CTE, SQL Joins, SQL Scripts
Previous Post
SQL SERVER – mssqlsystemresource – Resource Database
Next Post
SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

Related Posts

95 Comments. Leave new

  • Please help me in this.. i have written cte but facing problem in achieving my desired output:
    organizationtable:
    oid, orgname, level, parentid
    10-unit1-1-0
    11-dept1-2-10
    12-dept2-2-10
    13-sec1-3-11
    empdtls:
    eid staffno oid
    1-99-13

    cte which i have writted is below:
    with testorg as(
    select oid,orgname,parentid from #organizationdtls where parentid = 0
    union all
    select a.oid,a.orgname,a.parentid from #organizationdtls a inner join testorg t on(a.parentid = t.oid)
    )
    select t.* from testorg t

    Help me in achieving such output if i pass oid = 10 then it should return
    empdtls:
    eid staffno oid
    1-99-13

    Please help me !!

    Reply
  • Thanks Pinal.

    Reply
  • I still didn’t the solution for this.. Plz help.

    Table has following 2 columns

    DocNum DocEntry
    1 234
    2 324
    2 746
    3 876
    3 764
    4 100
    4 387

    Expected result is as follow

    1 234
    2 324, 746
    3 876, 764
    4 100, 387

    Reply
    • Vishal Motwani
      June 8, 2016 4:56 pm

      create table #tbl1(DocNum TINYINT, DocEntry INT)
      INSERT INTO #tbl1 VALUES
      (1, 234),
      (2, 324),
      (2, 746),
      (3, 876),
      (3, 764),
      (4, 100),
      (4, 387)

      SELECT * FROM #tbl1

      select DocNum,STUFF((SELECT ‘,’ + cast(DocEntry as varchar)
      from #tbl1 #tbl11
      where #tbl11.DocNum = #tbl1.DocNum
      FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)
      ,1,1,”)
      from #tbl1
      group by DocNum

      Reply
  • Maja Stojanova
    March 4, 2012 3:05 am

    Hi, can you please explain me why in this example in the recursive member stands p.PerAssemblyQty and not bom.PerAssemblyQty?

    WITH Parts (AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
    (
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
    INNER JOIN Parts AS p
    ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL
    )
    SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
    ComponentLevel
    FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
    ORDER BY ComponentLevel, AssemblyID, ComponentID;
    GO

    Thank you in advance

    Reply
  • Hello All

    I have two tables like below. This is something like parent child relationship. Table 1 have relation details and Table 2 have ids for each row.

    Table 1

    Col1 Col2 col3
    A A1 A1.1
    B B1 B1.1
    B B1 B1.2
    C C1 C1.1
    C C2 C2.1
    C C3 C3.1

    Table 2
    Col1 KEY
    A 1
    A1 2
    A1.1 3
    B 4
    B1 5
    B1.1 6
    B1.2 7
    C 8
    C1 9
    C1.1 10
    C2 11
    C2.1 12
    C3 13
    C3.1 14

    Extecpted output

    CHILD PARENT

    14 13
    12 11
    10 9
    13 8
    11 8
    9 8
    7 5
    6 5
    5 4
    3 2
    2 1
    NULL 1

    how to get it using quyer

    Reply
  • CREATE TABLE [dbo].[Jogo](
    [Jogo] [float] NULL,
    [Casa] [float] NULL,
    [Coluna1] [nvarchar](3) NULL,
    [Coluna2] [nvarchar](3) NULL,
    [Coluna3] [nvarchar](3) NULL
    ) ON [PRIMARY]

    GO

    Jogo,Casa,Coluna1,Coluna2,Coluna3
    255,1,C1:,NULL,NULL
    255,2,A1:,B1:,C1:
    255,3,A1:,NULL,NULL
    255,4,A1:,C1:,NULL
    255,5,A1:,NULL,NULL
    255,6,C1:,B1:,NULL
    255,7,A1:,C1:,NULL
    255,8,A1:,NULL,NULL
    255,9,A1:,NULL,NULL
    255,10,A1:,B1:,C1:
    255,11,A1:,NULL,NULL
    255,12,C1:,NULL,NULL
    255,13,A1:,NULL,NULL
    255,14,A1:,C1:,NULL
    255,15,A1:,NULL,NULL

    Jogo Casa Coluna1 Coluna2 Coluna3
    255 1 C1: NULL NULL
    255 2 A1: B1: C1:
    255 3 A1: NULL NULL
    255 4 A1: C1: NULL
    255 5 A1: NULL NULL
    255 6 C1: B1: NULL
    255 7 A1: C1: NULL
    255 8 A1: NULL NULL
    255 9 A1: NULL NULL
    255 10 A1: B1: C1:
    255 11 A1: NULL NULL
    255 12 C1: NULL NULL
    255 13 A1: NULL NULL
    255 14 A1: C1: NULL
    255 15 A1: NULL NULL

    Using SQL generate all possible between the columns 1, 2 and 3.
    This is an example of practical utilization of query recurssive applied to sports lottery (loteca).
    The result should be 144 bet as element 15 in the column should be based on CASA.

    FIRST LINE == B1:A1:A1:A1:A1:B1:A1:A1:A1:A1:A1:B1:A1:A1:A1:
    LAST LINE == B1:B1:A1:B1:A1:CA1::B1:A1:A1:B1:A1:B1:A1:B1:A1:

    Reply
  • Good article…

    Reply
  • Hello,

    I have a requirment where the hierarchy structure is defined in the below way.

    QA Sponsor
    QA Fund 1
    QA Fund 2
    QA Fund 3
    QA Fund 4

    QA Fund 5
    QA Fund 1

    Below is the proposed db design.

    Child Id Level Parent
    QA Sponsor 1
    QA Fund1 2 QA Sponsor
    QA Fund 2 3 QA Fund1
    QA Fund 3 4 QA Fund 2
    QA Fund 4 5 QA Fund 3

    QA Fund 5 2 QA Sponsor
    QA Fund1 2 QA Fund 5

    I have written the below CTE code and which provide me the o/p which is mentioned after the code.

    ;WITH cte AS
    (
    SELECT CAST(” + Name AS VARCHAR(100)) as ‘Name’, ID
    FROM dbo.RELATION
    WHERE PARENT_ID =”

    UNION ALL

    SELECT CAST(cte.Name + ‘—>’ + t.Name AS VARCHAR(100)), t.ID
    FROM dbo.RELATION t
    INNER JOIN cte ON t.parent_id = cte.id
    )
    SELECT Name FROM cte
    ORDER BY ID

    O/P

    QA SPONSOR
    QA SPONSOR—>QA FUND1
    QA SPONSOR—>QA FUND1—>QA FUND2
    QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3
    QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4
    QA SPONSOR—>QA FUND5
    QA SPONSOR—>QA FUND5—>QA FUND1

    Everything looks ok however but since QA Fund 1 has fund 2,3,and 4 inside it. there should be one more line i.e. QA SPONSOR—>QA FUND5—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4

    I am stuck and not sure what should i do to get around this problem. Please help me in this.

    Reply
  • I found query but it use 3 select query but to improve performance is there any way so i can retrieve answer less than 3 select query? Either by with cte or rownumber or rank or partition by?

    Reply
  • hi pinal can u please revert back with a query to find a palindrome of a string using CTE recursive function
    thanks in advance. .
    really need it

    Reply
  • Hi Pinal,
    I need to get userdetails from mytable who havent done any transactions in last
    N Months . transaction details and userdetails are maintained in different tables.

    Reply
  • Can You tell me the query How to find out what are the child tables suppose if I have given table Name
    Example Suppose If I have given table name like emp
    I need to find out what are the child tables for employeeclass

    Reply
  • Nick - SQL Developer
    January 17, 2013 9:49 pm

    I am trying to extract numeric values from a field (NodePath) that are seperated by decimal points. For example, .1.2.56.4235.8571. each numeric value represents a unique identifyer (NodeId) for a folder or file in an application. The db I’m pulling the data from (objectstore..Lar_Tree) has the NodeId (int), the NodePath (varchar(900)) and the NodeName (varchar(128)). Once I have the numeric values extracted I want to concatenate them into a path for documentation. Someone suggested using a recursive query, but I have not written one yet and can’t seem to get it to work based on the example above. Any help would be greatly appreciated.

    Reply
    • Nick - SQL Developer
      January 17, 2013 9:53 pm

      …btw…This is how I was approching it prior to hearing I should use a recursive query.

      PRINT’List all Wiz DBs; Lar_Names, DB_Name, Types, CensusYear’

      SELECT
      NodeName,
      NodeId,
      LEN(NodePath) – LEN(REPLACE(NodePath, ‘.’, ”)) AS DCount,
      NodePath,
      Left(objectstore..LAR_Tree.NodeName,40)AS Lar_Name,
      Left(objectstore..LAR_Items.DatabaseName,10) AS Database_Name,
      objectstore..LAR_Items.LARType, objectstore..LAR_Items.CensusYear
      INTO #tbl_Temp
      FROM objectstore..LAR_Tree
      Left JOIN objectstore..LAR_Items
      ON objectstore..LAR_Tree.NodeId = objectstore..LAR_Items.ItemId
      ORDER BY Database_Name

      SELECT
      NodePath,
      CensusYear,
      Database_Name,
      (CASE
      WHEN DCount = 3 THEN (CASE
      WHEN LEN(NodePath) = 5 THEN
      (SELECT NodeName
      FROM objectstore..LAR_Tree
      WHERE NodeId = CAST(SUBSTRING(NodePath,4,1)AS INT))
      WHEN LEN(NodePath) = 6 THEN
      (SELECT NodeName
      FROM objectstore..LAR_Tree
      WHERE NodeId = CAST(SUBSTRING(NodePath,4,2)AS INT))
      WHEN LEN(NodePath) = 7 THEN
      (SELECT NodeName
      FROM objectstore..LAR_Tree
      WHERE NodeId = CAST(SUBSTRING(NodePath,4,3)AS INT))
      WHEN LEN(NodePath) = 8 THEN
      (SELECT NodeName
      FROM objectstore..LAR_Tree
      WHERE NodeId = CAST(SUBSTRING(NodePath,4,4)AS INT))
      END)

      END) AS GUI_Path
      FROM #tbl_Temp

      Reply
  • Great Pinal!
    You are god of sql

    Reply
  • i have a table called test :- create table test (id int , Docname varchar(200), parentkey int, ContentType varchar(200))
    insert into test values (1,’ParentFolder’,-3,’Folder’)
    insert into test values (2,’ChildFolder1′,1,’Folder’)
    insert into test values ( 3,’ChildFolder1′,2,’Folder’)
    insert into test values (4,’ParentFolder 2′,-3,’Folder’)
    insert into test values (5,’test Folder 1′,4,’Folder’)
    insert into test values (6,’test Folder 2′,4,’Folder’)

    in this table id is the primarykey, i have a column called parentkey in this table, in this table records having parent-child relation

    ex:- id Name PrentKey
    1 test -3
    2 test2 1

    -3 is the parentkey here, 1 vlaue in the parentkey is the child of id 1,

    here if i give child id key i want to get the all names of that child n parent fileds. example :- if i give ‘2’ as input parament i want out put like below :

    ID Path
    1-2 test-test2

    for this i m trying below query :-

    WITH Emp_CTE ( id,ParentKey,ContentType,Path,Level)
    AS (

    SELECT id , ParentKey,ContentType,
    CONVERT(varchar(1000),DocName) , 0 Level
    FROM test S
    WHERE id = 2

    UNION ALL

    SELECT SS.ID , SS.ParentKey,SS.ContentType,
    CONVERT(varchar(1000), ISNULL(SS.DocName,”)+’|’+ISNULL(Path,”) )
    ,Level +1
    FROM test SS
    INNER JOIN Emp_CTE ecte ON ecte.ParentKey = SS.ID
    )

    SELECT CASE WHEN Path IS NULL THEN ” ELSE ‘|’ END + Path as folderpath,
    * FROM Emp_CTE WHERE ContentType = ‘Folder’ AND ParentKey = -3

    ablove query is giving output as below :-

    ID Path
    1 test|test2

    here i want to display all the IDs , but i am able to append all the docnames from table , how to achive id’s like 1-2?

    Reply
  • Hello,

    I have to use table valued function twice in a same SELECT statement. So will l SQL retrieve data once or twice if the same function is used in two places?

    If it retrieves twice will CET resolves it?
    Thank you!

    Reply
  • Hi Pinal,
    the above example can be done even with joins and groupby. how about the below be more apt example for cte?

    with CI AS (SELECT t.name,t.object_id,count(t.object_id) CI
    FROM sys.indexes i inner join sys.tables t
    on i.object_id = t.object_id
    where t.type like ‘u’ and i.type_desc like ‘CLUSTERED’
    group by t.name,t.object_id)
    , NCI AS (SELECT t.name,t.object_id,count(t.object_id) NCI
    FROM sys.indexes i inner join sys.tables t
    on i.object_id = t.object_id
    where t.type like ‘u’ and i.type_desc like ‘NONCLUSTERED’
    group by t.name,t.object_id)
    SELECT t.name,CI.CI,NCI.NCI,CI.CI + NCI.NCI TI
    FROM sys.tables t inner join CI on CI.Object_id = t.object_id
    inner join NCI on NCI.object_id = t.object_id
    where t.type like ‘u’
    order by TI desc

    select t.name,count(i.index_id)
    from sys.tables t inner join sys.indexes i on i.object_id = t.object_id
    where i.type_desc in (‘CLUSTERED’,’NONCLUSTERED’)
    group by t.name
    order by 2 desc

    Reply
  • i have table eid without using case statement in sql server
    eid
    _______
    0
    1
    0
    1
    i nee out put
    _______
    1
    0
    1
    0

    could plz any help this

    Reply
    • DECLARE @table table (eid int)
      INSERT INTO @table
      VALUES (1),(0),(1),(0),(1),(0)
      SELECT * FROM @table

      select CONVERT(bit, eid-1) as NewEid
      FROM @table

      Reply
    • Vishal Motwani
      June 8, 2016 5:14 pm

      Hi Bhumireddy…below is the answer to your query…replace 0’s and 1’s without case statement

      create table #tbl2(val int)
      insert into #tbl2 values
      (0),
      (1),
      (0),
      (1)

      select * from #tbl2

      ;
      with cte
      as
      (
      select replace(val,’0′,’2′) val1
      from #tbl2
      )
      , abc
      as
      (
      select replace(val1,’1′,’0′) val2
      from cte
      )
      , xyz
      as
      (
      select replace(val2,’2′,’1′) val3
      from abc
      )
      select * from xyz

      Reply
  • How to understand the Estimated Execution plan in SQL Server and also

    what is purpose of KeeyLookup ,HashJoin,NestedLoop,FillFactor,Sort in Exccution Plans
    could plz elaborate that

    Reply
  • Here is my idea of Simple.
    WITH cte AS
    (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n + 1 <= 10
    )
    SELECT n
    FROM cte

    I think this helps people understand it a bit better.

    Reply

Leave a Reply