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.
Thanks Pinal.
Now I am able to understand it
very useful !
Thanks alot.
Very interesting!
Hi,
Can u tell me the books to study about SQL SERVER for the beginner.
thanks alot
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?
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
hi Ravi/ Sathya
I am looking for the same output. can you please help me out with this query?
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
[...] 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) [...]
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 ?
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!
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
This exact question was in the MCTS 70-433 exam. Did you take it from there or did you microsoft used it from here??
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.
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
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
PLEASE HELP ME FOR ABOVE PASTED PROBLEM
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.
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).
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
all managers are also employees so their id, also appears in empid column and managers name appears in empname collumn
.
nice example,
Can Mr.Pinal or anyone tell me how CTE took advantage over temporary tables. Temporay tables VS CTE.
Pros and cons
Like Oracle, is it possible to get ROWID in SQL Server…
If so how?
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
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;
[...] 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 [...]
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/
Thank you so much.. It was really helpful
Excellent example. Thanks.
Really useful information. Really helped me a lot to understand the concept of CTEs
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.
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
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?
[...] SQL SERVER – Simple Example of Recursive CTE [...]
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
Thank you so much, It is very useful.
Sorry for my english,
How can I use CTE in SSAS ,creating a named query on a DSV?
is there a way to avoid a loop (nocycle for those of you who know oracle plsql) using cte?
[...] 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) [...]
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 …
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.
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
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
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.
Very usefull thanks..
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 !!
[...] (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 [...]
Thanks Pinal.