Execute the following
T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate how to apply recursive CTE for organizational chart processing.
-- SQL recursive query - recursive CTE - find chain of command
-- T-SQL recursive Common Table Expression - tree processing
USE AdventureWorks;
DECLARE @EmployeeID AS INT;
SET @EmployeeID = 100;
WITH EmployeeCTE(Name,EmployeeID,ManagerID)
AS (SELECT LastName + ', ' + FirstName,
EmployeeID,
ManagerID
FROM HumanResources.Employee AS E
JOIN Person.Contact AS C
ON C.ContactID = E.ContactID
WHERE EmployeeID = @EmployeeID
UNION ALL
SELECT C.LastName + ', ' + C.FirstName,
HRE.EmployeeID,
HRE.ManagerID
FROM HumanResources.Employee AS HRE
JOIN Person.Contact AS C
ON C.ContactID = HRE.ContactID
JOIN EmployeeCTE AS E
ON E.ManagerID = HRE.EmployeeID)
SELECT ChainOfCommand=Name
FROM EmployeeCTE;
GO
/* ChainOfCommand
Sacksteder, Lane
Li, Yuhong
Krebs, Peter
Hamilton, James
Sánchez, Ken
*/
------------
-- Microsoft SQL Server T-SQL recursive Common Table Expression - CTE
-- T-SQL organinational chart, orgchart, tree processing
-- MSSQL inner join, left join, self join
-- T-SQL select from select, derived table, subselect, subquery
USE AdventureWorks;
WITH cteEmployeeManager(EmployeeID,Name,ManagerID,LEVEL)
AS (-- Anchor (root) Member (AM)
SELECT EmployeeID,
c.LastName + ', ' + c.FirstName,
ManagerID,
0
FROM Person.Contact c
JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
WHERE ManagerID IS NULL
UNION ALL
-- Recursive Member (RM)
SELECT e.EmployeeID,
c.LastName + ', ' + c.FirstName,
e.ManagerID,
M.LEVEL + 1
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN cteEmployeeManager AS M
ON E.ManagerID = M.EmployeeID)
SELECT CEO = l0.Name,
Executive = l1.Name,
Manager = l2.Name,
Supervisor = ISNULL(l3.Name,''),
Staff = ISNULL(l4.Name,'')
FROM (SELECT Name,
EmployeeID,
ManagerID
FROM cteEmployeeManager
WHERE LEVEL = 0) l0
LEFT JOIN (SELECT Name,
EmployeeID,
ManagerID
FROM cteEmployeeManager
WHERE LEVEL = 1) l1
ON l1.ManagerID = l0.EmployeeID
LEFT JOIN (SELECT Name,
EmployeeID,
ManagerID
FROM cteEmployeeManager
WHERE LEVEL = 2) l2
ON l2.ManagerID = l1.EmployeeID
LEFT JOIN (SELECT Name,
EmployeeID,
ManagerID
FROM cteEmployeeManager
WHERE LEVEL = 3) l3
ON l3.ManagerID = l2.EmployeeID
LEFT JOIN (SELECT Name,
EmployeeID,
ManagerID
FROM cteEmployeeManager
WHERE LEVEL = 4) l4
ON l4.ManagerID = l3.EmployeeID
ORDER BY l1.Name,
l2.Name,
l3.Name,
l4.Name
GO
Related article:
Recursive Queries Using Common Table Expressions
|
