SQL SERVER – Simple Example of Recursive CTE « Journey to SQL Authority with Pinal Dave

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.

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

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