This morning I had to do some recursive common table experessions. It is acctually relativly simple to do.
A common scenario:
We have a parent child relation in a selfreferencing table. That is common when we have an employee manager relationship or organisation relationship.
Let’s just look at the employee manager relationship.
Let’s create an example tmp table like so
CREATE TABLE #Employee ( EmployeeID INT, EmployeeName NVARCHAR(50), ManagerID INT )
And let’s insert some test data into the table
INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (1,'Mr. Manager',NULL) INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (2,'Jensen',1) INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (3,'Hansen',1) INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (4,'Clausen',2)
Where, as you can see, Mr. Manager is at the top, and Clausen is at the bottom.
We now want to use as recursive common table expression(CTE) to show what level a person is on, in the employee hierarchy.
To do that, we start with the highest level(Where managerID is Null). Then we do a union all with a select which is referencing it self, like so:
WITH EmployeeCTE (EmployeeID,EmployeeName, ManagerID , Level) AS ( SELECT EmployeeID, EmployeeName,ManagerID, 0 AS Level FROM #Employee WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.EmployeeName,e.ManagerID, Level+1 FROM #Employee e INNER JOIN EmployeeCTE cte ON cte.EmployeeID=e.ManagerID ) SELECT * FROM EmployeeCTE
And the result can be seen below. This SQL shows what level an employee is on.
EmployeeID EmployeeName ManagerID Level 1 Mr. Manager NULL 0 2 Jensen 1 1 3 Hansen 1 1 4 Clausen 2 2
If we want to see an employees way to the top, we can select from the same CTE like so:
SELECT lvl0.EmployeeName as lvl0Name, lvl1.EmployeeName AS lvl1Name, lvl2.EmployeeName AS lvl2Name FROM EmployeeCTE lvl2 INNER JOIN EmployeeCTE lvl1 ON lvl1.EmployeeID=lvl2.ManagerID INNER JOIN EmployeeCTE lvl0 ON lvl0.EmployeeID=lvl1.ManagerID WHERE lvl2.Level=2
And the result:
lvl0Name lvl1Name lvl2Name Mr. Manager Jensen Clausen
Now you know how to do recursive CTE 🙂