从基本的员工/主管层级开始，我使用递归CTE来建立级别：
WITH EmployeeSupervisor
AS (
SELECT *
FROM (
VALUES ('mike','lisa')
,('kevin','lisa')
,('lisa','ken')
,('ken','scott')
,('scott','chris')
,('chris','')
) RawData(emp, sup)
)
,Hier
AS (
-- anchor level, no supervisor
SELECT 1 AS lvl
,emp
,sup
FROM EmployeeSupervisor
WHERE sup = ''
UNION ALL
-- recursive member
SELECT H.lvl + 1 AS lvl
,ES.emp
,ES.sup
FROM EmployeeSupervisor ES
INNER JOIN Hier H
ON ES.sup = H.emp
WHERE H.lvl + 1 <= 5 -- max of 5 levels
AND ES.sup != ''
)
SELECT *
FROM Hier
我已经尝试在带有COALESCE的PIVOT上进行一些变体以获取所需的输出（如下面的查询所示），但没有成功。
-- expected output
SELECT *
FROM (
VALUES ('mike','lisa','ken','scott','chris')
,('kevin','lisa','ken','scott','chris')
,('lisa','ken','scott','chris', NULL)
,('ken','scott','chris', NULL, NULL)
,('scott','chris', NULL, NULL, NULL)
,('chris',NULL, NULL, NULL, NULL)
) Expected(lvl1, lvl2,lvl3,lvl4,lvl5)
那里有很多类似的问题，但没有一个能真正解决这个问题的本质。
如果想要所有emp的层次结构，则必须以所有emp开头，而不仅是根目录。然后旋转很简单。
WITH EmployeeSupervisor
AS (
SELECT *
FROM (
VALUES ('mike','lisa')
,('kevin','lisa')
,('lisa','ken')
,('ken','scott')
,('scott','chris')
,('chris','')
) RawData(emp, sup)
)
,Hier
AS (
-- all employees
SELECT 1 AS lvl
,emp
,sup
FROM EmployeeSupervisor
UNION ALL
-- recursive supervisors
SELECT H.lvl + 1 AS lvl
,H.emp
,ES.sup
FROM EmployeeSupervisor ES
JOIN Hier H
ON ES.emp = H.sup
WHERE H.lvl < 5 -- max of 5 levels
AND ES.sup <> ''
)
SELECT *
FROM Hier
pivot (max(sup)
for lvl in ([1], [2], [3], [4], [5])
) as pvt
见小提琴
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句