CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query.  In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function.  In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks



Create Table VehicleCategory


ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)




Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS


      SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1


            UNION ALL


      SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

            INNER JOIN

            cte_VehicleCategory AS cv ON v.ParentID=cv.ID


Select * from cte_VehicleCategory


Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value  in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query 😉 than change statement after “INNER JOIN”  to “cte_VehicleCategory AS cv ON v.ID=cv.ID”


Reference: Ritesh Shah

5 Responses to “CTE (Common Table Expression) SQL-Server 2005”

  1. Ed Tabara Says:

    man, this is a bomb!
    and right when i needed it!
    thx a lot. i changed it slightly and got the code i needed so much.

  2. vithal mavuluri Says:

    It’s really good article

  3. LUIS Says:

    tablea has id,name and parentid

    case when v.name is null then ‘ -parents without child-‘
    else v.name
    end parent_name,
    from tablea r,
    tablea v
    WHERE r.parentid *= v.id and
    order by 1
    I hope it helps you without using cte because in some environments that only access sqlserver 2005 i could not use cte, please let me know your opinion.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: