SQL Server中如何将多层级数据合并至同一行？

现有中间输出（树形结构父子关系） #

Parent    Child
P1        C1
P1        C2
C1        D1
C2        E1
D1        F1
E1        G1
F1        H1

期望输出（同一行展示完整层级） #

L0    L1      L2        L3      L4
P1    C1      D1        F1      H1
P1    C2      E1        G1

解决思路 #

1. 用递归CTE生成带层级标记的完整路径

先通过递归CTE遍历树形结构，给每个节点标记所属层级（L0到L4），同时记录每条分支的所有节点信息。递归时从根节点开始，逐层向下填充对应层级的字段：

WITH RecursiveCTE AS (
    -- 锚点：定位根节点（这里P1是无父节点的根）
    SELECT 
        Parent AS L0,
        Child AS L1,
        CAST(NULL AS VARCHAR(50)) AS L2,
        CAST(NULL AS VARCHAR(50)) AS L3,
        CAST(NULL AS VARCHAR(50)) AS L4,
        Child AS CurrentNode,
        1 AS LevelNum
    FROM YourTable
    WHERE Parent NOT IN (SELECT Child FROM YourTable)
    UNION ALL
    -- 递归：向下遍历子节点，填充对应层级字段
    SELECT 
        r.L0,
        r.L1,
        CASE WHEN r.LevelNum = 1 THEN t.Child ELSE r.L2 END,
        CASE WHEN r.LevelNum = 2 THEN t.Child ELSE r.L3 END,
        CASE WHEN r.LevelNum = 3 THEN t.Child ELSE r.L4 END,
        t.Child AS CurrentNode,
        r.LevelNum + 1 AS LevelNum
    FROM RecursiveCTE r
    JOIN YourTable t ON r.CurrentNode = t.Parent
)
-- 筛选出没有子节点的行，这些行包含完整分支的所有层级信息
SELECT L0, L1, L2, L3, L4
FROM RecursiveCTE r
WHERE NOT EXISTS (SELECT 1 FROM YourTable t WHERE t.Parent = r.CurrentNode);

2. 动态处理不确定层级的场景

如果树形结构的层级数量不固定，没法提前写死L0到L4，可以用动态SQL结合字符串拆分实现：

• 先通过递归生成每条完整分支的路径字符串（比如P1|C1|D1|F1|H1）
• 统计最大层级数，自动生成对应的L0到LN列名
• 拆分路径字符串，按位置对应到各层级列

示例代码框架：

DECLARE @MaxLevel INT;
DECLARE @Columns NVARCHAR(MAX) = '';
DECLARE @i INT = 0;

-- 生成所有分支的完整路径并统计最大层级
WITH RecursiveCTE AS (
    SELECT 
        Parent AS Root,
        CONCAT(Parent, '|', Child) AS Path,
        Child AS CurrentNode,
        2 AS LevelCount
    FROM YourTable
    WHERE Parent NOT IN (SELECT Child FROM YourTable)
    UNION ALL
    SELECT 
        r.Root,
        CONCAT(r.Path, '|', t.Child) AS Path,
        t.Child AS CurrentNode,
        r.LevelCount + 1 AS LevelCount
    FROM RecursiveCTE r
    JOIN YourTable t ON r.CurrentNode = t.Parent
)
SELECT @MaxLevel = MAX(LevelCount) FROM RecursiveCTE;

-- 构建动态列语句
WHILE @i < @MaxLevel
BEGIN
    SET @Columns += CONCAT(', L', @i, ' = MAX(CASE WHEN pos = ', @i + 1, ' THEN value END)');
    SET @i += 1;
END

-- 拼接并执行动态SQL
DECLARE @Sql NVARCHAR(MAX) = CONCAT('
WITH RecursiveCTE AS (
    SELECT 
        Parent AS Root,
        CONCAT(Parent, ''|'', Child) AS Path,
        Child AS CurrentNode
    FROM YourTable
    WHERE Parent NOT IN (SELECT Child FROM YourTable)
    UNION ALL
    SELECT 
        r.Root,
        CONCAT(r.Path, ''|'', t.Child) AS Path,
        t.Child AS CurrentNode
    FROM RecursiveCTE r
    JOIN YourTable t ON r.CurrentNode = t.Parent
),
SplitPaths AS (
    SELECT 
        Root,
        value,
        ROW_NUMBER() OVER(PARTITION BY Root, Path ORDER BY (SELECT NULL)) AS pos
    FROM RecursiveCTE
    CROSS APPLY STRING_SPLIT(Path, ''|'')
    WHERE NOT EXISTS (SELECT 1 FROM YourTable t WHERE t.Parent = CurrentNode)
)
SELECT Root AS L0', @Columns, '
FROM SplitPaths
GROUP BY Root, Path;
');

EXEC sp_executesql @Sql;

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