SQL Server中未知层级数据转路径式扁平（分支）结构方案咨询

嘿，这个需求我之前在项目里碰过好多次，针对SQL Server中未知层级的树形结构转成你要的扁平分支视图，咱们可以用递归CTE加上动态SQL来完美解决，不管你的层级是3层还是10层都能自动适配。

核心思路 #

首先我们得先遍历整个树形结构，拿到每个节点的完整路径，同时算出整个结构的最大层级深度；然后把每个叶子节点的路径补全到最大层级（不足的部分用节点自身名称填充，和你的示例输出一致）；最后动态生成对应数量的LVL列，把拆分后的路径映射到列里。

完整解决方案脚本 #

DECLARE @MaxDepth INT;
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 第一步：计算整个树形结构的最大层级深度
WITH RecursiveCTE AS (
    -- 锚点：定位根节点（这里根节点的父节点是#root）
    SELECT Dim_Name, Dim_Parent, 0 AS Depth
    FROM YourTable
    WHERE Dim_Parent = '#root'
    UNION ALL
    -- 递归：遍历所有子节点，累计深度
    SELECT t.Dim_Name, t.Dim_Parent, r.Depth + 1 AS Depth
    FROM YourTable t
    INNER JOIN RecursiveCTE r ON t.Dim_Parent = r.Dim_Name
)
SELECT @MaxDepth = MAX(Depth) FROM RecursiveCTE;

-- 第二步：生成所有叶子节点的完整填充路径
WITH RecursiveCTE AS (
    SELECT 
        Dim_Name AS NodeName,
        CAST(Dim_Name AS VARCHAR(MAX)) AS FullPath,
        0 AS CurrentDepth
    FROM YourTable
    WHERE Dim_Parent = '#root'

    UNION ALL

    SELECT 
        t.Dim_Name AS NodeName,
        CAST(r.FullPath + '|' + t.Dim_Name AS VARCHAR(MAX)) AS FullPath,
        r.CurrentDepth + 1 AS CurrentDepth
    FROM YourTable t
    INNER JOIN RecursiveCTE r ON t.Dim_Parent = r.NodeName
),
FullPathCTE AS (
    SELECT 
        NodeName,
        -- 补全路径到最大层级：如果当前深度不够，重复自身名称填充
        CAST(FullPath + REPLICATE('|' + NodeName, @MaxDepth - CurrentDepth) AS VARCHAR(MAX)) AS FinalPath
    FROM RecursiveCTE
    -- 只保留叶子节点（没有子节点的节点），和你的示例输出一致
    WHERE NOT EXISTS (SELECT 1 FROM YourTable t WHERE t.Dim_Parent = NodeName)
)
-- 第三步：动态生成SQL，把路径拆分成对应的LVL列
SELECT @DynamicSQL = N'
SELECT 
    ' + STRING_AGG(
        'PARSENAME(REPLACE(FinalPath, ''|'', ''.''), ' + CAST(@MaxDepth - i + 1 AS VARCHAR) + ') AS ' + QUOTENAME('LVL' + CAST(@MaxDepth - i AS VARCHAR)),
        ', '
    ) WITHIN GROUP (ORDER BY i) + N'
FROM FullPathCTE;'
FROM GENERATE_SERIES(0, @MaxDepth) AS i;

-- 执行动态SQL，输出结果
EXEC sp_executesql @DynamicSQL;

关键细节说明 #

1. 叶子节点筛选：脚本里通过NOT EXISTS判断节点是否有子节点，只输出叶子节点的路径，这和你的示例输出（两行都是叶子节点）完全匹配。
2. 路径补全逻辑：对于深度小于最大层级的节点（比如示例里的Assistant），会用自身名称重复填充剩余的层级，最终得到G. Manager|Assistant|Assistant|Assistant这样的完整路径。
3. 动态列生成：通过STRING_AGG和GENERATE_SERIES自动生成对应数量的LVL列，不管你的树形结构有多少层，都能自动适配。

处理长字符串/多层级的替代方案 #

如果你的节点名称很长或者层级超过4层（PARSENAME最多支持4个部分），可以把动态SQL里的拆分逻辑改成用STRING_SPLIT，替换掉原来的动态SQL部分：

SELECT @DynamicSQL = N'
WITH SplitPath AS (
    SELECT 
        FinalPath,
        VALUE AS Node,
        ROW_NUMBER() OVER (PARTITION BY FinalPath ORDER BY (SELECT NULL)) AS RN
    FROM FullPathCTE
    CROSS APPLY STRING_SPLIT(FinalPath, ''|'')
)
SELECT ' + STRING_AGG(
    '(SELECT Node FROM SplitPath WHERE FinalPath = fp.FinalPath AND RN = ' + CAST(i+1 AS VARCHAR) + ') AS ' + QUOTENAME('LVL' + CAST(@MaxDepth - i AS VARCHAR)),
    ', '
) WITHIN GROUP (ORDER BY i) + N'
FROM FullPathCTE fp;';

这样就能处理更长的路径和更多的层级了。

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