March 25, 2008

Recursive Common Table Expression

Common Table Expression (CTE) as you know is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. If you are new to CTE's, read the BOL over here.

In this sample, we will see how to use Recursive CTE's to find out the Grade/Band of an employee in a company.

Case Scenario : We have a table called Employees. We need to display the Grade/Band of each Employee in the company. The Grade will be determined by the hierarchy of Managers above the person.
For the sake of demonstrating CTE's, we will create the following columns in the Employees table - ID, EName, Designation and ManagerID. Using recursive CTE, we will then display the Grade of each employee in the company. Grade 1 is the highest grade, given only to those who do not have managers above them.


-- create temporary table called Employees
ID int Identity(1,1) PRIMARY KEY,
EName varchar(50),
Designation varchar(50),
ManagerID int NULL

-- Insert some sample records in the Employees table
INSERT INTO #Employees VALUES('Paul','ED',NULL);
INSERT INTO #Employees VALUES('Goldman','CTO',1);
INSERT INTO #Employees VALUES('Jeniffer','VP Technology',1);
INSERT INTO #Employees VALUES('Sally','VP Sales',2);
INSERT INTO #Employees VALUES('Jack','VP Marketing',2);
INSERT INTO #Employees VALUES('Maner','Tech Head',4);
INSERT INTO #Employees VALUES('Jaidy','Sales Head',5);
INSERT INTO #Employees VALUES('Parry','Marketing Head',6);
INSERT INTO #Employees VALUES('Roger','Developer',7);
INSERT INTO #Employees VALUES('Yuan','Developer',7);
SELECT * FROM #Employees;

-- Use Recursive CTE to find out the Grade of each employee
WITH CTE(ID, EmployeeName, Designation, ManagerID, Grade)
SELECT ID, Ename, Designation, ManagerID, 1 as Grade
From #Employees WHERE ManagerID is NULL
SELECT e.ID, e.Ename, e.Designation, e.ManagerID, c.Grade + 1
FROM #Employees e
ON e.ManagerID = c.ID
SELECT ID, EmployeeName, Designation, ManagerID, Grade

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


Chetan said...

How do I recursively INSERT rows in a table based on an existing tree(contains id and parentid) values?

Suprotim Agarwal said...

Please elaborate more on that with an example.

SQL said...

CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine. Follow the link to know the details…