SQL Server中如何基于层级结构生成透视化层级列？

针对你的需求，核心问题在于层级数量可能不固定，静态PIVOT无法适配动态列的场景。以下是两种可行的解决方案，覆盖静态和动态层级的情况：

一、静态透视（层级数量固定） #

如果你的层级数是已知且固定的（比如最多3层），可以直接用静态PIVOT语句，注意必须保证每个层级路径有唯一的分组标识（比如根节点ID），这是很多人用PIVOT失败的关键。

示例代码 #

假设你的递归CTE生成的中间表名为HierarchyCTE，包含RootID（根节点标识，用于分组）、Level（层级数）、NodeID（当前层级的节点值）：

SELECT 
    RootID,
    [1] AS [Level 1],
    [2] AS [Level 2],
    [3] AS [Level 3]
FROM (
    -- 子查询提供透视所需的基础数据
    SELECT RootID, Level, NodeID
    FROM HierarchyCTE
) AS SourceData
PIVOT (
    -- 用MAX/MIN聚合，因为每个RootID+Level组合只会有一个值
    MAX(NodeID)
    -- 指定要透视的列（层级数）
    FOR Level IN ([1], [2], [3])
) AS PivotResult;

二、动态透视（层级数量不固定） #

如果层级数是动态变化的，SQL Server没有原生的动态透视函数，需要用动态SQL拼接透视语句，步骤如下：

1. 确保递归CTE包含根节点标识 #

如果你的中间表还没有RootID，需要在递归CTE中生成它，用于后续分组：

WITH HierarchyCTE AS (
    -- 锚点：根节点（根据你的数据调整判断条件，比如ParentID IS NULL）
    SELECT 
        ChildID AS NodeID,
        ParentID,
        1 AS Level,
        ChildID AS RootID  -- 根节点的RootID为自身ID
    FROM YourInputTable
    WHERE ParentID IS NULL
    UNION ALL
    -- 递归：子节点继承根节点ID
    SELECT 
        t.ChildID AS NodeID,
        t.ParentID,
        cte.Level + 1 AS Level,
        cte.RootID
    FROM YourInputTable t
    INNER JOIN HierarchyCTE cte ON t.ParentID = cte.NodeID
)
SELECT * INTO #TempHierarchy FROM HierarchyCTE; -- 临时表方便后续操作

2. 动态生成透视列并执行SQL #

DECLARE @PivotCols NVARCHAR(MAX), @SelectCols NVARCHAR(MAX), @DynamicSQL NVARCHAR(MAX);

-- 生成PIVOT需要的列列表（如[1], [2], ...）
SELECT @PivotCols = STRING_AGG(QUOTENAME(Level), ', ')
FROM (SELECT DISTINCT Level FROM #TempHierarchy) AS Levels
ORDER BY Level;

-- 生成查询结果的别名列（如[Level 1], [Level 2], ...）
SELECT @SelectCols = STRING_AGG(QUOTENAME(Level) + ' AS ' + QUOTENAME('Level ' + CAST(Level AS VARCHAR)), ', ')
FROM (SELECT DISTINCT Level FROM #TempHierarchy) AS Levels
ORDER BY Level;

-- 拼接完整的动态SQL语句
SET @DynamicSQL = N'
SELECT 
    RootID,
    ' + @SelectCols + N'
FROM (
    SELECT RootID, Level, NodeID
    FROM #TempHierarchy
) AS SourceData
PIVOT (
    MAX(NodeID)
    FOR Level IN (' + @PivotCols + N')
) AS PivotResult;';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

-- 清理临时表
DROP TABLE #TempHierarchy;

兼容低版本SQL Server（2016及以下）

如果你的SQL Server版本低于2017，不支持STRING_AGG，可以用STUFF + FOR XML PATH来拼接列：

-- 生成PivotCols
SELECT @PivotCols = STUFF((
    SELECT ', ' + QUOTENAME(Level)
    FROM (SELECT DISTINCT Level FROM #TempHierarchy) AS Levels
    ORDER BY Level
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 生成SelectCols
SELECT @SelectCols = STUFF((
    SELECT ', ' + QUOTENAME(Level) + ' AS ' + QUOTENAME('Level ' + CAST(Level AS VARCHAR))
    FROM (SELECT DISTINCT Level FROM #TempHierarchy) AS Levels
    ORDER BY Level
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

关键注意事项 #

• 必须有根节点标识列（RootID）：用于将同一层级路径的节点聚合到同一行，否则PIVOT会把所有数据合并成一行，导致结果错误。
• 聚合函数选择：因为每个RootID + Level组合只会有一个节点值，用MAX或MIN效果一致，任选其一即可。

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