如何使用SQL Server 2008建立一个祖先的累积“派生”列hierarchyid

Question

想象一下你典型的经理/员工层次结构，你有一个雇员与一个老板，他又有一个老板，而老板又有一个老板老板。

你会如何编写一个查询，该查询将包含所有老板名称的列作为varchar。

鉴于这一数据（而忽略了HIERARCHYID列，但这里的id列本质上描述该列）：

id | name   | bossid 
1 | BigBoss  | 0 
2 | CTO   | 1 
3 | CTO Lackey | 2 
4 | CIO   | 1 
5 | CIO Lackey | 4 

结束了这个结果集：

id | name   | all boss names 
1 | BigBoss  | 
2 | CTO   |Big Boss 
3 | CTO Lackey |Big Boss, CTO 
4 | CIO   |Big Boss 
5 | CIO Lackey |Big Boss, CIO 

谢谢！

Answer 1

既然你是在2008年SQL：

;WITH CTE_Tree AS 
(
    SELECT 
     id, 
     name, 
     '' AS boss_names 
    FROM 
     My_Table 
    WHERE 
     boss_id IS NULL 
    UNION ALL 
    SELECT 
     T.id, 
     T.name, 
     CTE.boss_names + ', ' + CTE.name 
    FROM 
     CTE_Tree CTE 
    INNER JOIN My_Table T ON 
     T.boss_id = CTE.id 
) 
SELECT 
    id, 
    name, 
    boss_names 
FROM 
    CTE_Tree 

这是从我的头顶，所以你可能需要调整它。它在boss_names的开头会有一个额外的逗号。你可以在你的最终查询中删除它，或者在CTE中检查CTE.boss_names，以决定是否加上逗号。