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
