SQL Server 2005 CTE (Common Table Expression)

SQL Server 2005 CTE (Common Table Expression)

By Bihag Thaker

CTE is a new feature provided by Microsoft SQL Server 2005. In real world, we often need to query hierarchical data from the database. For example, to get a list of hierarchical list of all the employees, list of product categories etc. CTE fulfills this requirement and let us query the database recursively.

Demo Builder - Create Flash Presentations
Create interactive Flash movies that allow you to show how applications and systems work. Download a FREE trial now.

Let’s us do this practically. Assume that we store different categories of computer books and any category can have sub-categories. For this, we will create a table named tblCategories with the following structure and insert some categories into this table as shown below:

Create Table tblCategories
(
CategoryID Int Constraint PK_tblCategories_CategoryID Primary Key,
CategoryName VarChar(100),
ParentCategoryID Int Constraint FK_tblCategories_ParentCategoryID References tblCategories(CategoryID)
)

GO

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(1,'Languages',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(2,'Networking',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(3,'Databases',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(4,'Visual Basic',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(5,'C#',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(6,'Java',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(7,'VB.Net',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(8,'VB 6.0',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(9,'Desktop Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(10,'Web Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(11,'ActiveX Objects and VB 6.0',8)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(12,'Network Security',2)

Now if you query the database with the following SELECT command,

Select * From tblCategories Where CategoryID = 1

You will get the following result:

CategoryID CategoryName ParentCategoryID
----------- ---------------- ----------------
1 Languages NULL

(1 row(s) affected)

No surprise! But what, if you want to get the list of all the categories/sub-categories falling under the root category ‘Languages’? To do this, you will need to perform a recursive query and to do that we use CTE.

Let’s do this with the help of CTE. To create CTE we will use the following syntax:

With cteCategories
AS (
Select CategoryID,CategoryName,ParentCategoryID
From tblCategories
Where CategoryID=1

Union All

Select C.CategoryID,C.CategoryName,C.ParentCategoryID
From tblCategories As C Inner Join cteCategories As P On C.ParentCategoryID = P.CategoryID

)

Select CategoryID,CategoryName,ParentCategoryID From cteCategories

Run the above query and see the result as shown below:

CategoryID CategoryName ParentCategoryID
----------- ---------------------------------------------------- ----------------
1 Languages NULL
4 Visual Basic 1
5 C# 1
6 Java 1
7 VB.Net 4
8 VB 6.0 4
11 ActiveX Objects and VB 6.0 8
9 Desktop Application Development with VB.Net 7
10 Web Application Development with VB.Net 7

(9 row(s) affected)