SQL SERVER – Simple Example of Recursive CTE | SQL Server Journey with SQL Authority

« SQL SERVER – mssqlsystemresource – Resource Database

SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop »

SQL SERVER – Simple Example of Recursive CTE

July 28, 2008 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged CTE | 78 Comments

78 Responses

on July 28, 2008 at 11:42 am | Reply Kamran Shahid

Thanks Pinal.
Now I am able to understand it
on July 29, 2008 at 5:59 pm | Reply Niyaz

very useful !

Thanks alot.
on July 29, 2008 at 6:37 pm | Reply manumik

Very interesting!
on July 30, 2008 at 3:04 pm | Reply Jessy . K

Hi,
Can u tell me the books to study about SQL SERVER for the beginner.
on August 4, 2008 at 2:21 pm | Reply ayman yassin

thanks alot
on August 5, 2008 at 4:07 pm | Reply Preeti Muley

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?
on December 27, 2008 at 8:18 pm | Reply Rahul Jain

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
- on August 16, 2010 at 12:22 pm | Reply Sathya Narayanan
  
  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
  - on May 28, 2011 at 6:33 pm | Reply ravi
    
    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
    - on November 2, 2011 at 9:10 am | Reply ram
      
      hi Ravi/ Sathya
      I am looking for the same output. can you please help me out with this query?
      - on February 7, 2012 at 6:13 pm ramesh :)
        
        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
- on October 1, 2012 at 3:01 pm | Reply syedbarkath
  
  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
on December 31, 2008 at 4:48 pm | Reply Palanivel

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
on February 26, 2009 at 5:35 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 6 Journey to SQL Authority with Pinal Dave

[...] CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. (Read More Here) [...]
on March 17, 2009 at 2:44 am | Reply Real Drouin

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 ?
on April 18, 2009 at 12:20 pm | Reply 怀恋春晨

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
- on February 25, 2010 at 9:59 pm | Reply Smelly
  
  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.
  - on January 24, 2011 at 9:55 pm | Reply Richard Hansell
    
    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!
on April 22, 2009 at 4:55 pm | Reply Shriram

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
on June 23, 2009 at 2:54 am | Reply marco

This exact question was in the MCTS 70-433 exam. Did you take it from there or did you microsoft used it from here??
on October 9, 2009 at 1:43 am | Reply Neel

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.
on December 31, 2009 at 4:20 am | Reply Kash

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.
- on December 31, 2009 at 4:23 am | Reply Kash
  
  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
on January 20, 2010 at 11:46 am | Reply tarni

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
on January 25, 2010 at 3:13 pm | Reply tarni

PLEASE HELP ME FOR ABOVE PASTED PROBLEM
- on January 27, 2010 at 7:10 pm | Reply k vamsi
  
  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.
on January 27, 2010 at 7:22 pm | Reply k vamsi

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).
on February 5, 2010 at 9:35 am | Reply tarni

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
on February 5, 2010 at 11:13 am | Reply tarni

all managers are also employees so their id, also appears in empid column and managers name appears in empname collumn
.
on March 6, 2010 at 10:43 am | Reply sivakumar s s

nice example,

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

Pros and cons
on June 3, 2010 at 11:01 am | Reply Surendra

Like Oracle, is it possible to get ROWID in SQL Server…
If so how?
on June 18, 2010 at 5:11 am | Reply Sanjeev

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
on July 16, 2010 at 3:04 am | Reply Uziel

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 ;)
- on December 12, 2011 at 7:59 pm | Reply nilesh
  
  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.
  - on December 12, 2011 at 9:15 pm | Reply diksta33
    
    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
    - on December 12, 2011 at 9:36 pm | Reply diksta33
      
      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;
on August 16, 2010 at 7:01 am | Reply SQL SERVER – Finding the Occurrence of Character in String Journey to SQL Authority with Pinal Dave

[...] hope using this hint along with recursive CTE you will be able to solve the T-SQL Challenge. Additionally, make sure that your strings does not [...]
on August 17, 2010 at 11:32 am | Reply SQL Lion

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/
on September 9, 2010 at 3:22 pm | Reply Varsha

Thank you so much.. It was really helpful
on November 6, 2010 at 12:11 am | Reply Kevin

Excellent example. Thanks.
on November 28, 2010 at 2:07 am | Reply Muhammad Imran

Really useful information. Really helped me a lot to understand the concept of CTEs
on December 5, 2010 at 7:31 am | Reply Rizi

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.
on January 31, 2011 at 6:55 pm | Reply jx

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
on January 31, 2011 at 6:56 pm | Reply jx

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?
on May 10, 2011 at 7:46 am | Reply SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

[...] SQL SERVER – Simple Example of Recursive CTE [...]
on May 18, 2011 at 1:21 pm | Reply Manish

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
on May 19, 2011 at 11:50 am | Reply satish

Thank you so much, It is very useful.
on May 28, 2011 at 3:55 am | Reply tynicool

Sorry for my english,

How can I use CTE in SSAS ,creating a named query on a DSV?
on June 27, 2011 at 7:32 pm | Reply Ilan

is there a way to avoid a loop (nocycle for those of you who know oracle plsql) using cte?
on July 15, 2011 at 7:01 am | Reply SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31 Journey to SQLAuthority

[...] CTE is the abbreviation for Common Table Expression. A CTE is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. (Read more here) [...]
on July 19, 2011 at 12:55 pm | Reply Roshni

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 …
on July 28, 2011 at 11:36 pm | Reply Will

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.
on July 31, 2011 at 1:37 pm | Reply Gautam Kansal

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
- on October 22, 2012 at 10:51 am | Reply Sandeep
  
  Hi Gautam,
  have you got answer for this question….?
  Please , help me out, if you got answer
on August 26, 2011 at 12:19 am | Reply Abhishek

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
- on June 12, 2012 at 2:33 pm | Reply Veeren
  
  R u able to resolve this?
on November 21, 2011 at 5:28 pm | Reply Pooja

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.
on December 7, 2011 at 10:15 am | Reply jatin chawla

Very usefull thanks..
on December 27, 2011 at 11:48 pm | Reply sam

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 !!
on January 27, 2012 at 7:01 am | Reply SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

[...] (CTE) and Few Observation Multiple CTE in One SELECT Statement Query Delete Duplicate Rows Simple Example of Recursive CTE SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite [...]
on January 27, 2012 at 3:28 pm | Reply satish kumar

Thanks Pinal.
on February 8, 2012 at 7:02 am | Reply SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

[...] on CTE: Simple Example of Recursive CTE Multiple CTE in One SELECT Statement Query Common Table Expression (CTE) and Few Observation Delete [...]
on February 21, 2012 at 3:59 am | Reply Vick

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
on March 4, 2012 at 3:05 am | Reply Maja Stojanova

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
on March 22, 2012 at 11:39 am | Reply SURA

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
on April 5, 2012 at 5:24 pm | Reply cOLL

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:
on April 19, 2012 at 3:05 pm | Reply Reena

Good article…
on May 15, 2012 at 1:21 am | Reply aman

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.
on July 6, 2012 at 11:21 am | Reply darpan

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?
on September 5, 2012 at 1:09 pm | Reply commissary

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
on October 15, 2012 at 3:56 pm | Reply Sonali

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.
on November 20, 2012 at 5:10 pm | Reply Koti rddy

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
on January 17, 2013 at 9:49 pm | Reply Nick - SQL Developer

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.
- on January 17, 2013 at 9:53 pm | Reply Nick - SQL Developer
  
  …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
on February 4, 2013 at 10:13 pm | Reply pravesh

Great Pinal!
You are god of sql
on February 28, 2013 at 11:49 am | Reply ravikumar

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?
on March 21, 2013 at 5:27 pm | Reply Arun

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!
on April 12, 2013 at 8:54 pm | Reply Bhavik Shah

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