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.
95 Comments. Leave new
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?
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
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
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