如何用PIVOT转换CTE生成的层级数据并导出至Excel？

方案1：动态SQL PIVOT（适配层级扩展） #

因为层级是动态的（当前4级，未来可能更多），静态PIVOT会因层级变化频繁修改代码，动态SQL可自动适配所有存在的Level值。

实现代码 #

-- 获取所有层级值，用于构建动态列
DECLARE @Levels NVARCHAR(MAX), @PivotCols NVARCHAR(MAX);

SELECT @Levels = STRING_AGG(QUOTENAME(Level), ', ')
FROM (SELECT DISTINCT Level FROM CategoryHierarchy) AS L;

SELECT @PivotCols = STRING_AGG(
    CONCAT('MAX(CASE WHEN Level = ', Level, ' THEN Name ELSE ''-'' END) AS [Level', Level, ']'),
    ', '
)
FROM (SELECT DISTINCT Level FROM CategoryHierarchy) AS L
ORDER BY Level;

-- 构建并执行动态PIVOT语句
DECLARE @Sql NVARCHAR(MAX) = CONCAT(
'WITH CategoryHierarchy AS (
    SELECT Id, Name, ParentId, 0 AS Level
    FROM [Categories]
    WHERE Id IN (24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37)
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, ch.Level + 1
    FROM [Categories] AS c
    INNER JOIN CategoryHierarchy AS ch ON c.ParentId = ch.Id
)
SELECT ', @PivotCols, '
FROM CategoryHierarchy
GROUP BY Id;');

EXEC sp_executesql @Sql;

说明 #

代码会自动根据CTE中的Level数量生成对应列，每个层级填充对应名称，无对应项显示-，按Id分组确保每行对应一个节点的完整层级路径。

方案2：递归CTE直接构建层级列（更直观） #

无需PIVOT，直接在递归过程中把各层级名称存储到对应列，最终结果直接符合Excel格式要求。

实现代码 #

WITH CategoryHierarchy AS (
    -- 基础层级（Level 0）
    SELECT 
        Id, 
        Name AS Level0,
        CAST('-' AS NVARCHAR(100)) AS Level1,
        CAST('-' AS NVARCHAR(100)) AS Level2,
        CAST('-' AS NVARCHAR(100)) AS Level3,
        ParentId,
        0 AS Level
    FROM [Categories]
    WHERE Id IN (24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37)

    UNION ALL

    -- 递归子层级
    SELECT 
        c.Id,
        ch.Level0, -- 继承父级的Level0
        CASE WHEN ch.Level = 0 THEN c.Name ELSE ch.Level1 END AS Level1,
        CASE WHEN ch.Level = 1 THEN c.Name ELSE ch.Level2 END AS Level2,
        CASE WHEN ch.Level = 2 THEN c.Name ELSE ch.Level3 END AS Level3,
        c.ParentId,
        ch.Level + 1
    FROM [Categories] AS c
    INNER JOIN CategoryHierarchy AS ch ON c.ParentId = ch.Id
)
-- 保留每个节点的完整层级状态（取层级最深的行）
SELECT Id, Level0, Level1, Level2, Level3
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Level DESC) AS rn
    FROM CategoryHierarchy
) t
WHERE rn = 1
ORDER BY Level0, Level1, Level2, Level3;

说明 #

若未来层级超过4级，只需在基础层级添加对应LevelN列，递归部分修改CASE语句即可。通过ROW_NUMBER()过滤每个节点的最新层级行，确保每行是完整的层级路径。

方案3：Excel端处理（无需修改SQL） #

如果不想改动SQL，可直接导出CTE原始数据（Id, Name, ParentId, Level）到Excel，用Power Query构建层级：

• 将数据导入Power Query
• 选中ParentId和Id列，选择转换 > 层次结构 > 从表格创建层次结构
• 展开所有层级，将空值替换为-
• 加载回Excel即可

这种方式适合层级频繁变化的场景，无需调整SQL代码。

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