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 (http://blog.SQLAuthority.com), Special thanks to Jr. Developers at my organization who helped me to prepare example.

About these ads

85 thoughts on “SQL SERVER – Simple Example of Recursive CTE

  1. Hi Pinal I do have a query in Oracle as follows
    SELECT
    (BALLOT_DATA.BALLOT_FROM + (BALLOT_NO -1)) AS BALLOT_NO,
    ELECTION.ELEC_PK,
    ELECTION_AREAS.NAME AS ELECTION_AREA,
    BD_PK,
    BALLOT_DATA.PO_PK,
    BALLOT_DATA.SYSTEMID,
    PREFIX,
    SUFFIX,
    BALLOT_FROM,
    BALLOT_TO,
    BALLOT_NO AS RELATIVE_BALLOT_NO
    FROM
    (
    SELECT
    ROWNUM BALLOT_NO
    FROM
    DUAL CONNECT BY ROWNUM <= 10000) BALLOT_PAPERS,
    BALLOT_DATA, ELECTION, ELECTION_AREAS
    WHERE
    BALLOT_PAPERS.BALLOT_NO <= BALLOT_DATA.ORDINARY_COUNT AND
    ELECTION.ELEC_PK = 10 AND
    ELECTION_AREAS.ELA_PK = 35
    can you suggest me the solution?

  2. Hi,

    I’ve following problem, which I’ve not been able to do successfully. Your help will be appreciated.

    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

    Thanks
    Rahul Jain

    • Hi Rahul Jain,

      Here is the answer :

      select distinct
      x.id
      ,substring(
      (select textfield + ‘,’ from testtable y where x.id=y.id order by x.Id for xml path(”))
      ,0
      ,LEN((select textfield + ‘,’ from testtable y where x.id=y.id order by x.Id for xml path(”))))
      from testtable x

      Here is the output :

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

      Thanks and Regards
      Sathya

      • hi satya,

        i’m trying this code.but it shows an error i think it not works .will u check it and send me clearly

        thanks,
        ravi

          • Try this:

            create table #temp1 (rno int, points varchar(20))
            INSERT INTO #temp1 VALUES (1,234)
            INSERT INTO #temp1 VALUES(2,324)
            INSERT INTO #temp1 VALUES(2,746)
            INSERT INTO #temp1 VALUES(3,876)
            INSERT INTO #temp1 VALUES(3,764)
            INSERT INTO #temp1 VALUES(4,100)
            INSERT INTO #temp1 VALUES(4,834)

            select * from #temp1

            select distinct a.rno,
            substring(
            (select points + ‘,’ from #temp1 b where a.rno=b.rno order by a.rno for xml path(”)),0,
            LEN(
            (select points + ‘,’ from #temp1 b where a.rno=b.rno order by a.rno for xml path(”)))
            )
            from #temp1 a

          • try this:

            create table #tempTable (DocNum int, DocEntry varchar(20))
            INSERT INTO #tempTable VALUES (1,234)
            INSERT INTO #tempTable VALUES(2,324)
            INSERT INTO #tempTable VALUES(2,746)
            INSERT INTO #tempTable VALUES(3,876)
            INSERT INTO #tempTable VALUES(3,764)
            INSERT INTO #tempTable VALUES(4,100)
            INSERT INTO #tempTable VALUES(4,834)

            select * from #tempTable

            SELECT DocNum, STUFF((SELECT ‘, ‘ + DocEntry FROM #tempTable WHERE (DocNum = a.DocNum)
            FOR XML PATH (”))
            ,1,2,”) AS NameValues
            FROM #tempTable a
            GROUP BY DocNum

    • Create table Doc
      (
      DocNum int,
      DocEntry int
      )
      insert into Doc values(1,234),(2,324),(2,746),(3,876),(3,764),(4,100),(4,387)

      Select DocNUm
      ,left(DocEntry,len(DocEntry)-1)DocEntry
      from(
      Select distinct DocNum
      ,(
      Select Cast(DocEntry as varchar)+’,’ from Doc B where A.DocNum=B.DocNum for xml path(”)
      )DocEntry from Doc A
      )C

  3. Hi Pinal

    Is there any Possiblility to use CTE in entire Stored procedure like Temp table. if i use CTE table more than one time its showing ‘invalid object name’.

    Thanks & Regards
    R. Palanivel

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 6 Journey to SQL Authority with Pinal Dave

  5. It’s working well but I have an issue. Let say that I for each manager I want to sort the employee by hiring date.

    How can I do that ?

  6. Pinal,

    You would get the same result by running a simple SELECT…I still don’t see the usage of recursive CTE in this case, or is it just a different way of getting same result? Could you provive some scenarios for R. CTE? Thanks!

    SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
    FROM HumanResources.Employee
    order by ManagerID

    • I would agree with “怀恋春晨”s comment. I ran this query in Adventurework database. The employee table has 290 records, and By running this query I would expect a lot more than 290, I would expect for every manager that employee report to, the query shall keep looking for manager’s manager until there is no manager for a manager.

      Instead, this query actually returns exactly the same result you’d get by just select * from employee table. the total is still 290.

      • Yes, this query is pretty useless in this form. All it does is list out the contents of the table in the “right” order. However, we can easily “fix” it to make it much more useful:

        WITH Emp_CTE AS (
        SELECT EmployeeID, ManagerID, Title, 0 AS Depth
        FROM HumanResources.Employee
        WHERE ManagerID IS NULL
        UNION ALL
        SELECT e.EmployeeID, e.ManagerID, e.Title, ecte.Depth + 1 AS Depth
        FROM HumanResources.Employee e
        INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
        )
        SELECT *
        FROM Emp_CTE

        All I am doing is adding a little contextual information to the query and removing some of the junk we don’t really need. I hope the syntax is right as I don’t have a copy of the old AdventureWorks database to test it!

  7. Hi ,

    Can i use a CTE for this scenario:
    I have a transaction table with an id column and a name column and also i have master table with the same id column.
    I need to concatenate all names for each id present in both tables and display it as a comma separated string along with the id

    I dont want to use cursors or loops

    Please help.

    Thanks in advance

  8. This exact question was in the MCTS 70-433 exam. Did you take it from there or did you microsoft used it from here??

  9. Thanks for sharing it.. I was studing CTEs wnd WITH vigorously. Since CTEs are used for recursive executions, I tried to use CTE to solve my below problem, is there any way you think it could be done with CTE?

    table1:
    ack SeqId freq_type
    1 1 9
    1 2 9
    1 3 9
    1 4 9
    1 5 9
    1 6 5
    1 7 5
    1 8 5
    1 9 9
    1 10 9
    1 11 9

    Result should be:

    ack SeqId freq_type
    1 1 9
    1 6 5
    1 9 9

    The logic shoolud be keep looping freq_type column untill it’s last value changes..If changed then add it to resultset…

    I thought and concluded that result can be achived if SQL in recursive section could support ‘top’ clauses..

    any thoughts?

    thanks for all your articles.

  10. I have question for you.

    I would like to select the records based on the status code.
    in my table i have records like this
    id name status date
    1 name1 A
    1 name1 R
    2 Name2 A
    2 Name2 R

    3 Name3 A
    4 Name4 A

    if the last record status is R then i do not want to select.
    from this table i have see the result like this
    ID NAme Status
    3 Name3 A
    4 Name4 A

    Please help!
    Thank you in advance.

    • small correction. record 1 may have like this e
      1 Name1 A
      1 Name1 R
      1 Name1 A

      in this case we have to select.
      when last record status is R then i do not want to select

  11. hi,
    we have one employe table with emp_id,emp_name,manager_id and what i want is
    empname and his manageres name in the order they are reporting to with / separated like
    empname reportingorder
    anil rajiv/mahesh/anil

    • can you be a bit clear in this scenario, as it is confusing where the manager name column come from. give an example with few values for the emp table and the output for the case.

  12. hi tarni,
    can you please explain the scenario a bit clearly as it is confusing where the manager name column come from. please explain the scenario with some example data for the emp table and what output you expects from it (from the example data).

  13. hi,
    i have employee table with columns(empid,empname,mgrid,deptNo).
    mangares are also employees.
    say, tarni is the top manager and his mgrid is null,suppose ram is an employee,
    his manager is rahim , rahim’s manager is karim, karims manager is tarni then
    my Result Should look like this…
    EmpName Managers

    ram tarni\karim\rahim\ram
    karim tarni\karim
    rahim karim\rahim
    tarni null

  14. nice example,

    Can Mr.Pinal or anyone tell me how CTE took advantage over temporary tables. Temporay tables VS CTE.

    Pros and cons

  15. Here is my parser without loop::

    CREATE PROCEDURE [dbo].[usp_parser]
    @string NVARCHAR(MAX)
    AS

    SELECT @string = ””+REPLACE(@string,’/’,”’ ))AS ROWS UNION ALL SELECT RTRIM(LTRIM(”’)+””
    SELECT @string = ‘SELECT RTRIM(LTRIM( ‘ + @string + ‘)) AS ROWS’
    EXEC SP_EXECUTESQL @string

  16. Hi Pinal, i have a good one, i still can´t solve it and hope you can help me:

    This is my table:
    idConcept idParentConcept Description
    1 NULL Root Concept
    2 NULL Root Concept
    3 NULL Root Concept
    4 1 Child Level 1
    5 1 Child Level 1
    6 4 Child Level 2
    7 2 Child Level 1
    8 2 Child Level 1

    So if i use a recursive CTE i get this resultset after executing it

    idParentConcept idConcept Descripcion Level
    NULL 1 Root Concept 0
    NULL 2 Root Concept 0
    NULL 3 Root Concept 0
    1 4 Root Concept 1
    1 5 Root Concept 1
    2 7 Root Concept 1
    2 8 Root Concept 1
    4 6 Root Concept 2

    I´m going to use this to generate a static web menu, so the records must be ordered as a tree:

    Root Node 1
    Child Node 4
    Child Node 5
    Child Node 6<- with the order by solution don´t work
    Root Node 2
    Child Node 7
    Child Node 8

    I can solve this with an ORDER BY, and it shows well with jus 2 levels, but when add a 3rd level this fails, shows the 3rd level at the end of the result set.

    Hope you can help me Pinal.
    Thanks for share your knowledge ;)

    • i also have same problem. i too need the solution for same problem. using order by it is successful til 2nd level. please provide me the solution for same.

      • Try this:

        DECLARE @Temp TABLE (idConcept INT, idParentConcept INT, [Description] VARCHAR(50));
        INSERT INTO @Temp VALUES (1, NULL, ‘Root Concept’);
        INSERT INTO @Temp VALUES (2, NULL, ‘Root Concept’);
        INSERT INTO @Temp VALUES (3, NULL, ‘Root Concept’);
        INSERT INTO @Temp VALUES (4, 1, ‘Child Level 1′);
        INSERT INTO @Temp VALUES (5, 1, ‘Child Level 1′);
        INSERT INTO @Temp VALUES (6, 4, ‘Child Level 2′);
        INSERT INTO @Temp VALUES (7, 2, ‘Child Level 1′);
        INSERT INTO @Temp VALUES (8, 2, ‘Child Level 1′);

        WITH cte AS (
        SELECT
        idConcept,
        idParentConcept,
        [Description]
        FROM
        @Temp
        WHERE
        idParentConcept IS NULL
        UNION ALL
        SELECT
        t.idConcept,
        t.idParentConcept,
        t.[Description]
        FROM
        @Temp t
        INNER JOIN cte ON t.idParentConcept = cte.idConcept
        )
        SELECT
        c1.*
        FROM
        cte c1
        LEFT JOIN cte c2 ON c2.idConcept = c1.idParentConcept
        LEFT JOIN cte c3 ON c3.idConcept = c2.idParentConcept
        ORDER BY
        COALESCE(c3.idConcept, c2.idConcept, c1.idConcept),
        COALESCE(c2.idConcept, c1.idConcept),
        c1.idConcept;

        –idConcept idParentConcept Description
        –1 NULL Root Concept
        –4 1 Child Level 1
        –5 1 Child Level 1
        –6 4 Child Level 2
        –2 NULL Root Concept
        –7 2 Child Level 1
        –8 2 Child Level 1
        –3 NULL Root Concept

        • Then I thought you probably want the child with the id = 6 to come after its parent in the list, i.e. 1, 4, 6, 5, 2, 7, 8, 3.

          This will work for three levels (same table variable as before):

          WITH cte AS (
          SELECT
          idConcept,
          idParentConcept,
          NULL AS ParentParent,
          [Description]
          FROM
          @Temp
          WHERE
          idParentConcept IS NULL
          UNION ALL
          SELECT
          t.idConcept,
          t.idParentConcept,
          cte.idParentConcept,
          t.[Description]
          FROM
          @Temp t
          INNER JOIN cte ON t.idParentConcept = cte.idConcept)
          SELECT
          idConcept,
          idParentConcept,
          [Description]
          FROM
          cte
          ORDER BY
          COALESCE(ParentParent, idParentConcept, idConcept),
          CASE WHEN ParentParent IS NULL THEN idConcept ELSE idParentConcept END,
          idConcept;

  17. Pingback: SQL SERVER – Finding the Occurrence of Character in String Journey to SQL Authority with Pinal Dave

  18. CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine. Follow the link to know the details…
    h ttp://www.sqllion.com/2010/08/common-table-expressions-cte/

  19. Please forgive me for my ignorance, but I would like to know what is HumanResources in the above example. I know that Employee is the table and AdventureWorks is the database. Any help to a beginer would be appreciated.

  20. Thanks for the link,
    THe resulting dataset is
    2 Fuller NULL
    1 Davolio 2
    3 Leverling 2
    4 Peacock 2
    5 Buchanan 2
    8 Callahan 2
    6 Suyama 5
    7 King 5
    9 Dodsworth 5

  21. would it be possible to query the managers for
    9 Dodsworth 5?
    e.g. it would return
    2 Fuller Null
    5 Buchanan 2

    because Buchanan is his direct superier, and Fuller is Buchanans superior?

  22. Pingback: SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

  23. Hi all,

    I am having a temptable with 100 names in it, and I want to execute a SP with all the 100 names as the parameters of the temptable and the output of the SP I want to store in another temp table say(XtempTable).

    Can I achieve this using CTE. if yes..
    Please advice how can I go with that using CTE.

    Thanks
    Manish

  24. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31 Journey to SQLAuthority

  25. Hello Pinal,

    My Table Contains Such Type of Record
    ID Name ParentID
    1 Item 1 NULL
    2 Item 2 NULL
    3 Cate 1 1
    4 Cate 2 2
    5 Cate 3 1
    6 Cate 4 1
    7 Cate 5 1
    8 Cate 6 2
    …… so on …. may be there is possibility of child of Cate 1, Cate 2….

    Actually using CTE, It will Give me Output Like this :
    ID Name ParentID
    1 Item 1 NULL
    2 Item 2 NULL
    4 Cate 2 2
    8 Cate 6 2
    3 Cate 1 1
    5 Cate 3 1
    6 Cate 4 1
    7 Cate 5 1

    But I want OutPut Like this :
    ID Name ParentID
    1 Item 1 NULL
    3 Cate 1 1
    5 Cate 3 1
    6 Cate 4 1
    7 Cate 5 1
    2 Item 2 NULL
    4 Cate 2 2
    8 Cate 6 2

    How can I get Such Type of Output ??…. Please Help me …

    • DECLARE @table table (ID int, Name varchar(20),ParentID int, row int)
      INSERT INTO @table
      SELECT 1,’Item 1′, NULL, 0 UNION ALL
      SELECT 2,’Item 2′, NULL, 0 UNION ALL
      SELECT 3,’Cate 1′, 1, 0 UNION ALL
      SELECT 4,’Cate 2′, 2, 0 UNION ALL
      SELECT 5,’Cate 3′, 1, 0 UNION ALL
      SELECT 6,’Cate 4′, 1, 0 UNION ALL
      SELECT 7,’Cate 5′, 1, 0 UNION ALL
      SELECT 8,’Cate 6′, 2, 0

      UPDATE @table SET row = CASE WHEN ParentID IS null then ID ELSE ParentID end

      ;with Parents as (
      SELECT ID,Name,ParentID,row from @table where ParentID IS null
      UNION all
      SELECT [@table].ID,[@table].Name,[@table].ParentID ,[@table].row
      FROM @table inner join Parents on [@table].ParentID = Parents.ID
      )
      SELECT ID,Name,ParentID from Parents ORDER BY row,ID

  26. The CTE works well, but doesn’t give the order I need. The problem I have is that I want to order the records as follows:

    CEO
    Bob – Reports to CEO
    Joe – Reports to Bob
    Mike – Reports to Joe
    May – Reports to Bob
    Andy – Reports to CEO
    Tom – Reports to Andy

    The spaces are for clarity

    This is the code I am using at the moment:

    WITH GroupReports (GroupID, ReportsTo, GroupName, MemberID, Level)
    AS
    (
    – Anchor member definition
    SELECT e.GroupID, e.ReportsTo, e.GroupName, e.MemberID,
    0 AS Level
    FROM dbo.Groups AS e
    WHERE e.ReportsTo IS NULL AND e.MemberID = @GroupID
    UNION ALL
    – Recursive member definition
    SELECT e.GroupID, e.ReportsTo, e.GroupName, e.MemberID,
    Level + 1
    FROM Groups e
    INNER JOIN GroupReports d
    –ON e.GroupID = d.ReportsTo
    –ON e.ReportsTo = d.ReportsTo
    ON e.ReportsTo = d.GroupID
    )
    – Statement that executes the CTE
    SELECT gr.GroupID, gr.ReportsTo, gr.GroupName, gr.MemberID, Level
    FROM GroupReports gr

    And this is how it returns the rows:

    CEO
    Bob – Reports to CEO
    Andy – Reports to CEO
    May – Reports to Bob
    Joe – Reports to Bob
    Tom – Reports to Andy
    Mike – Reports to Joe

    It looks like all level one are selected, then all level two’s and so on. That is not what I want. I need to have them grouped by ‘Reports To’.

    Any help on this one would be much appreciated.

  27. Hi,

    I have a table with below data
    Column A Column B
    1 101
    1 102
    2 103
    2 104
    3 105
    3 106

    I need a result set as follows

    101, 103, 105
    101, 103, 106
    101, 104, 105
    101, 104, 106
    102, 103, 105
    102, 103, 106
    102, 104, 105
    102, 104, 106

    Can you please help me how to get the above result using CTE

  28. I have a transaction table: Tran

    Table Structure: Applid, SalesAmount, EmpId

    I have an employee table: Employee

    Table Structure: EmpId, EmpRole, ManagerId

    Employee table can have 4 emp roles: A, B, C, D (D is the highest role, A is the lowest role)

    The employee Id in Tran table (EmpId) can have employees with role A, B, C or D. Send me a query which would show the sum of SalesAmount for the employees at role D.

    Tran Table:
    Applid SalesAmount EmpID
    1 1000 1001
    2 2000 1002
    3 3000 1003
    4 4000 1004
    5 5000 1005
    6 6000 1006
    7 7000 1007
    8 8000 1008
    9 9000 1009
    10 10000 1001

    Employee:
    EmpID Role ManagerId
    1001 A 1002
    1002 B 1003
    1003 C 1005
    1004 A 1002
    1005 D —-
    1006 A 1007
    1007 B 1010
    1008 B 1010
    1010 C 1009
    1009 D —-

    Desired output:
    EmployeeID TotalSalesAmount
    1005 25000
    1009 30000

  29. hey i want a solution. please help.
    i have a table with these columns
    ID GroupName ParentId
    1 / 0
    2 a 1
    3 b 2
    4 a1 1
    5 b2 4

    and I want a output like

    Groupname
    /
    /a
    /a/b
    /a1
    /a1/b1

    please help me asap.

  30. 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 !!

  31. Pingback: SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

  32. Pingback: SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

  33. 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

  34. 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

  35. 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

  36. 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:

  37. 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.

  38. 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?

  39. 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.

  40. 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

  41. 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.

    • …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

  42. 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?

  43. 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!

  44. 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

    • 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

  45. 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

  46. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

  47. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s