|
Execute the following
script in Query Editor to create a tree for the mountain bike
manufacturer. Ken Sánchez is the CEO. Each path on the tree labelled with a unique label. The capital letter assignment to the immediate subordinates is done by the ROW_NUMBER function:
USE AdventureWorks;
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 = convert(varchar,''),
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
WHERE e.ManagerId IS NULL
) 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 = convert(varchar,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
FROM cteEmployeeTree
ORDER BY OrgChartPathLabel
|