此方法使用循环，但速度应该相对较快

• 它基于作业中的级别数量而不是个人来运行（所以如果你有3个像这里一样的级别，它会做3个循环）
• 读取和计算非常高效-它不需要执行大量的JOIN、排序和表扫描（除了第一次）

在this dbfiddle中，有一些运行这种方法的示例，并带有一些额外的报告

/*  Initial data setup  */

CREATE TABLE #Emps (ID int PRIMARY KEY, Job nvarchar(30), ParentID int);
INSERT INTO #Emps (ID, Job, ParentID) VALUES
(1,       N'CEO',                       NULL),
(2,       N'CFO',                       1),
(3,       N'CTO',                       1),
(4,       N'CMO',                       1),
(5,       N'Accounting Manager',        2),
(6,       N'Payroll Manager',           2),
(7,       N'Data Manager',              3),
(8,       N'Software Manager',          3),
(9,       N'Data Analyst',              7),
(10,      N'Data Engineer ',            7);

CREATE TABLE #EmpParents (RoundNum int, ID int, ParentID int, PRIMARY KEY (RoundNum, ID));

每个“轮”的数据（例如，循环中的迭代数）被插入到#EmpParents表中。
该方法是a）首先包括基本作业和父作业b）然后对于父作业，识别它们的父重复b）直到没有找到更多的父作业。
它查找“父的父”的方法是查看循环中的最后一个“轮”，以从该轮中获取每个作业的父ID;在下一轮中，它使用这些新的父节点来识别下一级父节点。
注意，我用WHILE循环来做这个，因为我认为它更容易阅读/理解。然而，你也可以用CURSOR或递归CTE来做这个--但它们毕竟是循环的变体。

/*  Data calculations - Find parents, and parents of parents  */

DECLARE @RoundNum int;     -- Used for counting what iteration we're up to
DECLARE @NumInserted int;  -- User for recording number of rows inserted

-- Insert initial data (own job and parent job)
INSERT INTO #EmpParents (RoundNum, ID, ParentID)
    SELECT  0, ID, ID
    FROM    #Emps
  UNION ALL
    SELECT  1, ID, ParentID
    FROM    #Emps
    WHERE   ParentID IS NOT NULL;

SET @NumInserted = @@ROWCOUNT;
SET @RoundNum = 1;

-- Add 'parents of parents' until no more found
WHILE @NumInserted > 0
    BEGIN

    SET @RoundNum += 1;

    INSERT INTO #EmpParents (RoundNum, ID, ParentID)
        SELECT  @RoundNum, #EmpParents.ID, #Emps.ParentID
        FROM    #EmpParents
                INNER JOIN #Emps ON #EmpParents.ParentID = #Emps.ID
        WHERE   #EmpParents.RoundNum = @RoundNum - 1
                AND #Emps.ParentID IS NOT NULL;

    SET @NumInserted = @@ROWCOUNT;

    END

请注意，在最终报告中，它使用最后一列（ParentID）作为主列，并查找与之对应的所有作业ID。

/*  Reporting  */

SELECT      ParentEmp.Job, STRING_AGG(TeamEmp.Job, ', ') AS Team
    FROM    #EmpParents
            INNER JOIN #Emps AS ParentEmp ON #EmpParents.ParentID = ParentEmp.ID
            INNER JOIN #Emps AS TeamEmp ON #EmpParents.ID = TeamEmp.ID
    GROUP BY #EmpParents.ParentID, ParentEmp.Job
    ORDER BY #EmpParents.ParentID;

以下是结果

Job                            Team
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CEO                            CEO, CFO, CTO, CMO, Accounting Manager, Payroll Manager, Data Manager, Software Manager, Data Analyst, Data Engineer 
CFO                            Accounting Manager, Payroll Manager, CFO
CTO                            CTO, Data Manager, Software Manager, Data Analyst, Data Engineer 
CMO                            CMO
Accounting Manager             Accounting Manager
Payroll Manager                Payroll Manager
Data Manager                   Data Manager, Data Analyst, Data Engineer 
Software Manager               Software Manager
Data Analyst                   Data Analyst
Data Engineer                  Data Engineer

最后注意：这里没有错误检查，并且假设是一个层次结构（例如，你不能有一个父循环，例如，我的下属是我老板的老板）。你可能想在循环中加入一些错误检查/etc来控制它。一个简单的方法是，如果RoundNum达到一个不可能的不切实际的高数字（例如，50），那么它会因为错误而中止。

您可以使用递归CTE来执行此操作。
首先，获取所有的雇员，并使用递归为每个雇员获取其子行（如果有的话）。注意，这里我们没有进行任何循环检查，假设没有循环。
然后按开始时的顶级ID聚合它，并将其联接回主表。

WITH cte AS (
    SELECT e.ID, e.Job, e.ID AS TopID
    FROM Employee e

    UNION ALL

    SELECT e.ID, e.Job, cte.TopID
    FROM cte
    JOIN Employee e ON e.ParentID = cte.ID
)
UPDATE e
SET MyTeam = grouped.Jobs
FROM Employee e
JOIN (
    SELECT
      cte.TopID,
      STRING_AGG(cte.Job, ', ') AS Jobs
    FROM cte
    GROUP BY
      cte.TopID
) grouped ON grouped.TopID = e.ID;

您还可以通过计算CTE中的Level列来强制执行排序

WITH cte AS (
    SELECT e.ID, e.Job, e.ID AS TopID, 1 AS Level
    FROM Employee e

    UNION ALL

    SELECT e.ID, e.Job, cte.TopID, cte.Level + 1
    FROM cte
    JOIN Employee e ON e.ParentID = cte.ID
)
UPDATE e
SET MyTeam = grouped.Jobs
FROM Employee e
JOIN (
    SELECT
      cte.TopID,
      STRING_AGG(cte.Job, ', ') WITHIN GROUP (ORDER BY cte.Level) AS Jobs
    FROM cte
    GROUP BY
      cte.TopID
) grouped ON grouped.TopID = e.ID;

db<>fiddle