| How to
apply recursive queries for organizational charts? |
|
Execute the following
recursive script in Query Editor to get the "direct report"
organizational chart of AdventureWorks:
USE AdventureWorks;
GO
WITH cteDirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, EmployeeLevel = 0
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN cteDirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT Manager=CO.LastName+', '+CO.FirstName,
Employee=C.LastName+', '+C.FirstName, EmployeeLevel
FROM cteDirectReports DR
INNER JOIN HumanResources.Employee E
ON DR.EmployeeID = E.EmployeeID
INNER JOIN Person.Contact C
ON E.ContactID = C.ContactID
INNER JOIN HumanResources.Employee EM
ON DR.ManagerID = EM.EmployeeID
INNER JOIN Person.Contact CO
ON EM.ContactID = CO.ContactID
GO
;
|
| American Standard
in SQL Server 2005 Training |
|