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.

QUERY

-- create temporary table called Employees
CREATE TABLE #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('Bill','CEO',NULL);
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)
AS
(
SELECT ID, Ename, Designation, ManagerID, 1 as Grade
From #Employees WHERE ManagerID is NULL
UNION ALL
SELECT e.ID, e.Ename, e.Designation, e.ManagerID, c.Grade + 1
FROM #Employees e
INNER JOIN CTE c
ON e.ManagerID = c.ID
)
SELECT ID, EmployeeName, Designation, ManagerID, Grade
FROM CTE Order BY ID


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "Recursive Common Table Expression"
  1. Chetan said...
    April 14, 2008 at 9:54 AM

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

  2. Suprotim Agarwal said...
    April 16, 2008 at 4:32 AM

    Please elaborate more on that with an example.

  3. SQL said...
    August 16, 2010 at 11:35 PM

    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…
    http://www.sqllion.com/2010/08/common-table-expressions-cte/

  4. cleonlim said...
    August 4, 2014 at 11:10 PM

    i was waitin' for this type of article and i have gained some useful information from this site. thanks for sharing this information.

    www.n8fan.net

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions