求助：编写支持1-30级的MS SQL动态层级列转换查询

核心思路 #

先通过递归CTE拉取每个节点的完整父链（从根到当前节点），再用动态SQL生成最多30级的level_1到level_30列，自动填充对应层级名称，超出当前层级的列设为NULL。

1. 递归CTE提取全层级数据 #

用递归CTE遍历所有节点，把每个节点的每一级父节点（包括自身）的名称和层级序号拆出来，存入临时表方便后续处理：

WITH HierarchyCTE AS (
    -- 根节点（层级1）
    SELECT 
        id,
        current_level,
        name AS level_name,
        1 AS level_number,
        parent_id
    FROM YourTableName
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归遍历子节点，层级数递增
    SELECT 
        child.id,
        child.current_level,
        child.name AS level_name,
        parent.level_number + 1 AS level_number,
        child.parent_id
    FROM YourTableName child
    INNER JOIN HierarchyCTE parent ON child.parent_id = parent.id
)
SELECT * INTO #HierarchyLevels FROM HierarchyCTE;

这段代码会为每个节点生成N条记录（N为该节点的层级数），每条记录对应一级的名称和层级编号。比如一个3级节点，会有level_number=1（根）、2（父节点）、3（自身）三条记录。

2. 动态SQL生成目标列 #

由于层级数最多30级且需要动态列，必须用动态SQL拼接列定义。这里用STRING_AGG生成level_1到level_30的CASE语句，再聚合得到每个节点的单行结果：

DECLARE @MaxLevel INT = 30;
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @ColumnList NVARCHAR(MAX);

-- 生成所有level列的SQL片段
SET @ColumnList = STRING_AGG(
    CONCAT('MAX(CASE WHEN level_number = ', n, ' THEN level_name END) AS level_', n),
    ', '
) FROM (SELECT TOP(@MaxLevel) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) AS Numbers;

-- 拼接完整查询语句
SET @DynamicSQL = CONCAT(
'
SELECT 
    base.id,
    base.current_level,
    base.name,
    base.parent_id,
    ', @ColumnList, '
FROM YourTableName base
LEFT JOIN #HierarchyLevels hl ON base.id = hl.id
GROUP BY base.id, base.current_level, base.name, base.parent_id
ORDER BY base.id;'
);

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

-- 清理临时表
DROP TABLE IF EXISTS #HierarchyLevels;

关键说明 #

• 动态列生成：通过STRING_AGG批量生成level_1到level_30的列，每个列用CASE语句匹配对应层级的名称，没有对应层级时自动返回NULL
• 聚合处理：用GROUP BY把每个节点的多条层级记录合并为一行，确保每个节点只输出一条结果
• 兼容性：支持1-30级任意层级的节点，根节点只有level_1有值，其他列自动为NULL；3级及以上节点会完整填充从根到自身的所有层级名称

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