|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
to demonstrate the listing of all the nodes of a subtree. AdventureWorks organization tree is used to list all employees who directly or indirectly report to a manager. Recursive CTE is applied with root starting at @ManagerID.
USE AdventureWorks;
GO
CREATE PROC DirectlyOrIndirectlyReportTo
@ManagerID INT
AS
BEGIN
WITH cteEmployeeTree
AS (SELECT rootTree.EmployeeName,
rootTree.ManagerName,
rootTree.EmployeeId,
rootTree.ManagerId,
CONVERT(VARCHAR(MAX),rootTree.PathSequence) AS PathLabel
FROM (SELECT EmployeeName = c.FirstName + ' ' + c.LastName,
ManagerName = cm.FirstName + ' ' + cm.LastName,
e.EmployeeId,
e.ManagerId,
char(64 + ROW_NUMBER()
OVER(ORDER BY e.EmployeeId)) AS PathSequence
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
LEFT JOIN HumanResources.Employee em
ON e.ManagerID = em.EmployeeID
JOIN Person.Contact cm
ON em.ContactID = cm.ContactID
WHERE e.ManagerId = @ManagerID) rootTree
UNION ALL
SELECT subTree.EmployeeName,
subTree.ManagerName,
subTree.EmployeeId,
subTree.ManagerId,
PathLabel = subTree.PathLabel + CONVERT(VARCHAR(MAX),subTree.PathSequence)
FROM (SELECT EmployeeName = c.FirstName + ' ' + c.LastName,
ManagerName = cm.FirstName + ' ' + cm.LastName,
e.EmployeeId,
e.ManagerId,
cte.PathLabel,
PathSequence = char(64 + ROW_NUMBER()
OVER(ORDER BY e.EmployeeId))
FROM cteEmployeeTree cte
JOIN HumanResources.Employee e
ON e.ManagerId = cte.EmployeeId
JOIN Person.Contact c
ON e.ContactID = c.ContactID
JOIN HumanResources.Employee em
ON em.EmployeeID = e.ManagerID
JOIN Person.Contact cm
ON em.ContactID = cm.ContactID) subTree)
SELECT EmployeeName,
ManagerName,
OrgChartPathLabel = PathLabel,
EmployeeID,
ManagerID
FROM cteEmployeeTree
ORDER BY LEN(PathLabel),
ManagerName,
EmployeeName
END
GO
EXEC DirectlyOrIndirectlyReportTo
3
GO
-- Ken Sanchez, CEO, EmployeeID=109
EXEC DirectlyOrIndirectlyReportTo
109
GO
|
