Server将所有子ID连接到父记录中分隔的字符串中

Question

我有一个关于分层(邻接模型) SQL Server表的问题。此链接Get child records on one column演示如何使用所有子记录的连接列表填充父字段。因此，我桌子上的“儿童”栏变成：

ID ParentID Name   Children
1  NULL     Root   2,3
2  1        L2-1   4
3  1        L2-3   NULL
4  2        L3-4   6
5  3        L3-5   NULL
6  4        L4-6   NULL

有人能建议我如何做多个层次，即所有的后代，而不仅仅是直接的孩子？

也就是说，我希望这张桌子：

ID ParentID Name   Children
1  NULL     Root   2,3,4,5,6
2  1        L2-1   4,6
3  1        L2-3   5
4  2        L3-4   6
5  3        L3-5   NULL
6  4        L4-6   NULL

谢谢!

Answer 1

您可以使用下面的查询。我以为你的桌子名叫孩子。

它首先构建到顶部的父母列表(第一个查询)。然后将所有独立的分支放在一起(第二个查询)。

WITH temp (ID, ParentID, ChildrenChain)
AS
(
    SELECT ID, ParentID, CAST('' AS VARCHAR(100))
    FROM children
    WHERE NOT EXISTS (SELECT * FROM children cc WHERE cc.ParentID = children.ID)

    UNION ALL

    SELECT children.ID, children.ParentID, CAST((temp.ChildrenChain + ',' + CAST(temp.ID AS VARCHAR(100))) AS VARCHAR(100)) 
    FROM children
    INNER JOIN temp ON children.ID = temp.ParentID
)

SELECT DISTINCT ID,
    REPLACE(
        STUFF(
           (SELECT
                ',' + t2.ChildrenChain
                FROM temp t2
                WHERE temp.ID=t2.ID
                ORDER BY t2.ChildrenChain
                FOR XML PATH(''), TYPE
           ).value('.','varchar(max)')
           ,1,2, ''
        ), 
    ',,', ',') AS ChildValues
FROM temp
WHERE LEN(ChildrenChain) > 0

如果这对你不起作用，请告诉我。