How do you create a cumulative, "derived" column of ancestry using SQL Server 2008 hierarchyid

Question

Imagine your typical manager / employee hierarchy, where you have an employee with a boss, who in turn has a boss, who in turn has a boss.

How would you write a query that would have a column with, say, all the boss's names as a varchar.

Given this data (leaving out the hierarchyid column but the id column here essentially describes that column):

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

End up with this resultset:

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

Thanks!

Answer 1

Since you're on SQL 2008:

;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

This is off the top of my head, so you may need to tweak it. It will have an extra comma at the start of the boss_names. You can either remove that in your final query, or put in a check of CTE.boss_names in the CTE to decide whether or not to prepend the comma.