Binaryworld - Real world recursion example of CTE (Common Table Expression) used in ... [ SQL Server -> T-SQL ]

CTE is new powerful feature in SQL Server 2005.

Two advantages of CTE
1. Implement Recursion (See example)
This is the biggest advantage using CTE. Recursion was possible in SQL 2000 but you had to write UDF or nasty joins.

2. Reuse Query anywhere by name (Just like View or Temp Table).
This is nice to have but not really big for many developers but more you use it you will find it more cleaner than creating temp tables for two reasons.
1) Temp table requires create and cleanup code but CTE doesnt require create or cleanup

http://msdn.microsoft.com/en-us/library/ms190766.aspx

2) CTE only exists for Query level scope.

Example

I spent so much time spending real world example of recursion where you have to traverse N leveles in the Tree to get certain information. Many sites give example of Getting Mananger Info for Employee but it can be easily done with simple self join no need for CTE.

Look at the following example where you have several levels of information listed in one table (For energy company : Country->State->City->Stations-> ... ). Assume that you have to traverse Tree to get certain parent information for a given record. In our case get State name for Stations or any Children items of State?

Click here to copy the following block

/*

--- Sample hirarchy ----

USA
NC
Charlotte
Pine-1
Pine-2
SouthBlv-1
GA
Marietta
IndPark-1
IndPark-2

*/

Create table #Hirarchy(
ID int
,Type varchar(20)
,Name varchar(100)
,ParentID int
);

insert into #Hirarchy values(1,'COUNTRY','USA',NULL)
insert into #Hirarchy values(2,'STATE','GA',1)
insert into #Hirarchy values(3,'STATE','NC',1)
insert into #Hirarchy values(4,'CITY','Charlotte',3)
insert into #Hirarchy values(5,'STATION','Pine-1',4)
insert into #Hirarchy values(6,'STATION','Pine-2',4)
insert into #Hirarchy values(7,'STATION','SouthBlv-1',4)
insert into #Hirarchy values(8,'CITY','Marietta',2)
insert into #Hirarchy values(9,'STATION','IndPark-1',8)
insert into #Hirarchy values(10,'STATION','IndPark-2',8)
;
WITH CTE_Hirarchy(ID,Type,Name,ParentID,State, HPath) AS
(
Select ID,Type,Name,ParentID,Name As State,CAST(Name as varchar(100)) as HPath from #Hirarchy where Type='STATE'
UNION ALL
SELECT a.ID,a.Type,a.Name,a.ParentID,b.State,CAST(b.HPath + '->' + a.Name as varchar(100)) as HPath from #Hirarchy a JOIN CTE_Hirarchy b on a.ParentID=b.ID
)
Select * from CTE_Hirarchy Where Type='STATION'
go

Drop table #Hirarchy

/*

Output

ID Type Name ParentID State HPath
5 STATION Pine-1 4 NC NC->Charlotte->Pine-1
6 STATION Pine-2 4 NC NC->Charlotte->Pine-2
7 STATION SouthBlv-1 4 NC NC->Charlotte->SouthBlv-1
9 STATION IndPark-1 8 GA GA->Marietta->IndPark-1
10 STATION IndPark-2 8 GA GA->Marietta->IndPark-2

*/