如何在一行中生成SQL Server树形结构查询结果？

问题说明 #

现有一张含ParentId字段的Test表，表结构及初始化数据如下：

create table Test 
(
    Id int identity, 
    Name varchar(50) not null, 
    SName varchar(50) null, 
    ParentId int null 
)

insert into Test 
values ('aaa', 'bbb', null), ('adf', '22b', null), ('aad', 'bbsd',2),('asdsaa', 'bf', 3),('sdfs','sdf',3),('iopio','uiopio',3)

需要将该树形结构数据整合为一行结果，层级关系以Name:SName的形式嵌套展示，示例格式如下：

"aaa":"bbb", "adf":{"aad":{"asdsaa":"bf", "sdfs":"sdf", "iopio":"uiopio"}}

此前尝试的递归CTE查询逻辑错误，未得到预期结果：

WITH tree_view AS 
(
    SELECT 
        Id, ParentId, Name, SName 
    FROM 
        Test
    UNION ALL 
    SELECT 
        parent.Id, parent.ParentId, parent.Name, parent.SName 
    FROM 
        Test parent 
    JOIN 
        tree_view tv ON parent.ParentId = tv.Id 
)
SELECT DISTINCT * 
FROM tree_view

正确实现方案 #

以下SQL通过递归CTE构建树形层级，结合字符串聚合生成符合要求的单行嵌套格式结果：

WITH RecursiveTree AS (
    -- 初始化：选取所有根节点（ParentId为NULL）
    SELECT
        Id,
        Name,
        SName,
        ParentId,
        -- 根节点直接生成键值对
        CAST('"' + Name + '":"' + ISNULL(SName, '') + '"' AS NVARCHAR(MAX)) AS JsonSegment
    FROM Test
    WHERE ParentId IS NULL

    UNION ALL

    -- 递归：处理子节点，将子节点嵌套到父节点中
    SELECT
        t.Id,
        t.Name,
        t.SName,
        t.ParentId,
        -- 子节点生成包含子层级的嵌套结构
        CAST('"' + t.Name + '":{' + STRING_AGG(rt.JsonSegment, ', ') + '}' AS NVARCHAR(MAX)) AS JsonSegment
    FROM Test t
    INNER JOIN RecursiveTree rt ON t.Id = rt.ParentId
    GROUP BY t.Id, t.Name, t.SName, t.ParentId
)
-- 拼接所有根节点的结果为单行
SELECT STRING_AGG(JsonSegment, ', ') AS FinalResult
FROM RecursiveTree
WHERE ParentId IS NULL

关键说明 #

1. 递归逻辑修正：此前的CTE关联逻辑倒置，正确逻辑应为从根节点出发，关联子节点（t.Id = rt.ParentId），逐层构建嵌套结构。
2. 格式拼接：通过字符串拼接结合STRING_AGG函数，将子节点的层级结构嵌套到父节点中，最终拼接所有根节点得到单行结果。
3. 空值处理：用ISNULL(SName, '')避免SName为NULL时的格式错误。

内容的提问来源于stack exchange，提问作者Samvel Yeghikyan