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

Feed on: Posts; Comments

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://www.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 | 8 Comments

8 Responses to “SQL SERVER - Simple Example of Recursive CTE”

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

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

very useful !

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

Very interesting!
on July 30, 2008 at 3:04 pm 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 ayman yassin

thanks alot
on August 5, 2008 at 4:07 pm 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 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 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