How to update tree view in SQL Server

Question

Here is my SQL Server table

ID      Job                       ParentID    MyTeam
1       CEO                       NULL        
2       CFO                       1
3       CTO                       1
4       CMO                       1
5       Accounting Manager        2
6       Payroll Manager           2
7       Data Manager              3
8       Software Manager          3
9       Data Analyst              7
10      Data Engineer             7

I need to fill the MyTeam field this way

each job will have all people that job managing

CEO's team will be CEO, CFO, CTO, CMO, Accounting Manager, Payroll Manager, Data Manager, Software Manager, Data Analyst, Data Engineer

CFO's team will be CFO, Accounting Manager, Payroll Manager

CTO's team will be CTO, Data Manager, Software Manager, Data Analyst, Data Engineer

I built a loop on this data and contacted each job to its parent and so on

but this is too slow

Is there a faster one update statement to do that fast

Answer 1

You can use a recursive CTE for this.

First, get all of the employees, and for each one get its child rows if any, using the recursion. Note that we have not made any loop checking here, the assumption is that there are no loops.

Then aggregate it by the top ID that you started with, and join it back to the main table.

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;

You can also enforce an ordering by calculating a Level column in the CTE

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