sql查詢一個節點的所有升序

我有一張表，記錄如下：

ID   Name   parentId
1    A      Null
2    B      1
3    C      Null
4    D      3
5    E      4

我需要以下列格式輸出

ID   Name   ParentName 

1    A      Null  
2    B       A
3    c      Null
4    D      C
5    E      C->D

我嘗試使用以下 sql 查詢，但無法獲得準確的輸出。誰能幫幫我

With CTE AS
(
  select id, Name, CAST('' AS VARCHAR(MAX)) as Parent from @table
  union All
  select t.Id, t.Name , CTE.Parent+','+CAST(t.Name AS VARCHAR(MAX)) as parents from
  CTE c inner join @table t on t.parentid=c.id and c.parentid is not null
)
select * from cte

確保錨點設置正確（需要是根或最後的葉子）。您目前的錨集（來自 的第一個集UNION ALL）正在從表中檢索所有記錄，而不僅僅是邊界記錄。

由於您的關係是通過父母而不是孩子，因此錨點應該包含所有沒有父母的記錄。然後遞歸地加入每個孩子。

DECLARE @table TABLE (ID INT, name VARCHAR(1), parentID INT)

INSERT INTO @table
VALUES 
   (1, 'A', NULL),
   (2, 'B', 1),
   (3, 'C', NULL),
   (4, 'D', 3),
   (5, 'E', 4)

;With CTE AS
(
   -- Anchor (all grandfathers)
   SELECT
       ID = T.ID,
       Name = T.Name,
       ParentName = CONVERT(VARCHAR(MAX), NULL) -- Set the data type for the UNION
   FROM
       @table AS T
   WHERE
       T.parentId IS NULL

   UNION ALL

   -- Traverse through each children
   SELECT
       ID = T.ID,
       Name = T.Name,
       ParentName = ISNULL(C.ParentName + ' -> ', '') + C.Name
   FROM
       CTE AS C
       INNER JOIN @table AS T ON C.ID = T.parentId
)
SELECT 
   C.*
FROM 
   cte AS C
ORDER BY
   C.ID

結果：

ID  Name    ParentName
1   A       NULL
2   B       A
3   C       NULL
4   D       C
5   E       C -> D

引用自：https://dba.stackexchange.com/questions/214513