如何实现层级关系表的动态宽表转换（支持新增层级无需改查询）

针对层级表转宽表的需求，通用方案核心是递归CTE获取全路径数据 + 动态SQL自动生成层级列，无需每次新增层级修改查询，具体实现如下：

1. 表结构与测试数据 #

假设你的层级表名为team_hierarchy，结构及测试数据如下：

CREATE TABLE team_hierarchy (
    Team_Id INT PRIMARY KEY,
    Parent_Id INT,
    Team_Name VARCHAR(100),
    Level INT -- 层级：根节点为Level=1，子节点依次递增
);

INSERT INTO team_hierarchy VALUES
(1, NULL, '总部', 1),
(2, 1, '技术部', 2),
(3, 2, '前端组', 3),
(4, 2, '后端组', 3),
(5, 1, '人事部', 2),
(6, 5, '招聘组', 3),
(7, 6, '校园招聘组', 4);

2. 递归CTE收集全路径层级数据 #

用递归CTE遍历每个节点的所有祖先（含自身），将每个层级的Team_Id和Team_Name拆解为键值对形式的窄表数据：

WITH recursive_hierarchy AS (
    -- 锚点成员：根节点
    SELECT 
        Team_Id,
        Parent_Id,
        Team_Name,
        Level,
        CAST('Level' + CAST(Level AS VARCHAR) + '_TeamId' AS VARCHAR(20)) AS TeamId_ColName,
        CAST(Team_Id AS VARCHAR(20)) AS TeamId_Value,
        CAST('Level' + CAST(Level AS VARCHAR) + '_Name' AS VARCHAR(20)) AS Name_ColName,
        Team_Name AS Name_Value
    FROM team_hierarchy
    WHERE Parent_Id IS NULL

    UNION ALL

    -- 递归成员：遍历子节点并关联父节点层级数据
    SELECT 
        th.Team_Id,
        th.Parent_Id,
        th.Team_Name,
        th.Level,
        CAST('Level' + CAST(th.Level AS VARCHAR) + '_TeamId' AS VARCHAR(20)) AS TeamId_ColName,
        CAST(th.Team_Id AS VARCHAR(20)) AS TeamId_Value,
        CAST('Level' + CAST(th.Level AS VARCHAR) + '_Name' AS VARCHAR(20)) AS Name_ColName,
        th.Team_Name AS Name_Value
    FROM team_hierarchy th
    JOIN recursive_hierarchy rh ON th.Parent_Id = rh.Team_Id
)
-- 拆分为键值对记录
SELECT 
    Team_Id AS Current_TeamId,
    Team_Name AS Current_TeamName,
    ColName,
    ColValue
FROM recursive_hierarchy
UNPIVOT (
    ColValue FOR ColName IN (TeamId_ColName, Name_ColName)
) AS unpvt;

这段代码会输出每个节点对应的所有层级列名和值，比如校园招聘组会包含Level1_TeamId、Level1_Name直到Level4_TeamId、Level4_Name的键值对。

3. 动态SQL自动生成宽表查询 #

通过动态SQL拼接最多10层的列名，再Pivot生成宽表，实现自动适配层级变化：

DECLARE @max_level INT = 10; -- 设定最大支持层级
DECLARE @pivot_cols NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX);

-- 拼接所有层级的列名：LevelX_TeamId, LevelX_Name
WHILE @max_level >= 1
BEGIN
    SET @pivot_cols += QUOTENAME('Level' + CAST(@max_level AS VARCHAR) + '_TeamId') + ', '
                      + QUOTENAME('Level' + CAST(@max_level AS VARCHAR) + '_Name') + ', ';
    SET @max_level -= 1;
END
-- 移除末尾多余逗号
SET @pivot_cols = LEFT(@pivot_cols, LEN(@pivot_cols) - 2);

-- 拼接并执行动态SQL
SET @sql = N'
WITH recursive_hierarchy AS (
    SELECT 
        Team_Id,
        Parent_Id,
        Team_Name,
        Level,
        CAST(''Level'' + CAST(Level AS VARCHAR) + ''_TeamId'' AS VARCHAR(20)) AS TeamId_ColName,
        CAST(Team_Id AS VARCHAR(20)) AS TeamId_Value,
        CAST(''Level'' + CAST(Level AS VARCHAR) + ''_Name'' AS VARCHAR(20)) AS Name_ColName,
        Team_Name AS Name_Value
    FROM team_hierarchy
    WHERE Parent_Id IS NULL

    UNION ALL

    SELECT 
        th.Team_Id,
        th.Parent_Id,
        th.Team_Name,
        th.Level,
        CAST(''Level'' + CAST(th.Level AS VARCHAR) + ''_TeamId'' AS VARCHAR(20)) AS TeamId_ColName,
        CAST(th.Team_Id AS VARCHAR(20)) AS TeamId_Value,
        CAST(''Level'' + CAST(th.Level AS VARCHAR) + ''_Name'' AS VARCHAR(20)) AS Name_ColName,
        th.Team_Name AS Name_Value
    FROM team_hierarchy th
    JOIN recursive_hierarchy rh ON th.Parent_Id = rh.Team_Id
),
unpivoted_data AS (
    SELECT 
        Team_Id AS Current_TeamId,
        Team_Name AS Current_TeamName,
        ColName,
        ColValue
    FROM recursive_hierarchy
    UNPIVOT (
        ColValue FOR ColName IN (TeamId_ColName, Name_ColName)
    ) AS unpvt
)
SELECT 
    Current_TeamId,
    Current_TeamName,
    ' + @pivot_cols + '
FROM unpivoted_data
PIVOT (
    MAX(ColValue) FOR ColName IN (' + @pivot_cols + ')
) AS pvt
ORDER BY Current_TeamId;
';

EXEC sp_executesql @sql;

方案说明 #

• 递归CTE确保每个节点的全路径层级数据被完整收集；
• 动态SQL自动生成最多10层的列名，后续新增层级（不超过10）无需修改查询；
• 最终宽表包含Current_TeamId、Current_TeamName，以及从Level1_TeamId到Level10_Name的所有列，无数据的层级列显示为NULL。

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