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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook