使用Oracle SQL递归连接实现树形结构行转列

假设表结构 #

先明确两张表的基础结构（如果你的表字段不同，替换对应字段即可）：

• tree表：存储树形节点关系

  TreeID	ParentTreeID	ItemID
  1	NULL	101
  2	1	102
  3	2	103
  4	1	102

• itemnames表：存储ItemID与名称的映射

  ItemID	ItemName
  101	ItemA
  102	ItemB
  103	ItemC

步骤1：用递归CTE遍历树形结构，收集各级节点信息 #

通过递归CTE获取每个节点的完整路径、层级，以及对应层级的ItemID和名称：

WITH RecursiveTree AS (
    -- 锚点成员：根节点（ParentTreeID为NULL的节点）
    SELECT
        t.TreeID,
        t.ParentTreeID,
        t.ItemID,
        iname.ItemName,
        1 AS Level, -- 根节点为第1层
        -- 存储路径：格式为 "Level1_ItemID:Level1_Name,Level2_ItemID:Level2_Name..."
        CONCAT(t.ItemID, ':', iname.ItemName) AS PathInfo
    FROM tree t
    JOIN itemnames iname ON t.ItemID = iname.ItemID
    WHERE t.ParentTreeID IS NULL

    UNION ALL

    -- 递归成员：遍历子节点
    SELECT
        child.TreeID,
        child.ParentTreeID,
        child.ItemID,
        iname.ItemName,
        parent.Level + 1 AS Level,
        CONCAT(parent.PathInfo, ',', child.ItemID, ':', iname.ItemName) AS PathInfo
    FROM tree child
    JOIN RecursiveTree parent ON child.ParentTreeID = parent.TreeID
    JOIN itemnames iname ON child.ItemID = iname.ItemName
)
SELECT * FROM RecursiveTree;

这个CTE会输出每个节点的层级、完整路径信息，比如第3层节点的PathInfo会是 101:ItemA,102:ItemB,103:ItemC。

步骤2：将层级数据行转列为结构化表格 #

因为树形层级不固定，用动态SQL实现通用的行转列（以SQL Server为例）：

第一步：获取最大层级，生成列名 #

先查询树形结构的最大层级，用来动态生成Level1到LevelN的列：

DECLARE @MaxLevel INT;
SELECT @MaxLevel = MAX(Level) FROM RecursiveTree;

DECLARE @Cols NVARCHAR(MAX) = '';
DECLARE @i INT = 1;
WHILE @i <= @MaxLevel
BEGIN
    -- 生成每个层级的名称列和ItemID列
    SET @Cols += CONCAT(
        ', MAX(CASE WHEN Level = ', @i, ' THEN ItemName END) AS Level', @i, '_Name',
        ', MAX(CASE WHEN Level = ', @i, ' THEN ItemID END) AS Level', @i, '_ItemID'
    );
    SET @i += 1;
END;

第二步：执行动态SQL生成最终表格 #

把递归CTE和动态生成的列结合，得到结构化的行转列结果：

DECLARE @Sql NVARCHAR(MAX) = CONCAT('
WITH RecursiveTree AS (
    SELECT
        t.TreeID,
        t.ParentTreeID,
        t.ItemID,
        iname.ItemName,
        1 AS Level,
        CONCAT(t.ItemID, '':'', iname.ItemName) AS PathInfo
    FROM tree t
    JOIN itemnames iname ON t.ItemID = iname.ItemID
    WHERE t.ParentTreeID IS NULL

    UNION ALL

    SELECT
        child.TreeID,
        child.ParentTreeID,
        child.ItemID,
        iname.ItemName,
        parent.Level + 1 AS Level,
        CONCAT(parent.PathInfo, '', '', child.ItemID, '':'', iname.ItemName) AS PathInfo
    FROM tree child
    JOIN RecursiveTree parent ON child.ParentTreeID = parent.TreeID
    JOIN itemnames iname ON child.ItemID = iname.ItemName
)
SELECT
    TreeID,
    ParentTreeID,
    PathInfo',
    @Cols, '
FROM RecursiveTree
GROUP BY TreeID, ParentTreeID, PathInfo;
');

EXEC sp_executesql @Sql;

其他数据库的适配说明 #

• MySQL：用GROUP_CONCAT配合动态SQL，或者用JSON函数解析PathInfo来拆分列；
• PostgreSQL：可以用crosstab函数，或者通过string_to_array拆分PathInfo后转列；
• Oracle：用PIVOT结合动态SQL，或者XML函数处理动态列。

关键注意点 #

• 确保递归CTE的终止条件正确，避免死循环（比如Tree表中不要出现循环引用）；
• 如果同一层级有多个节点（比如分支），行转列时需要根据业务需求选择聚合函数（MAX/MIN等）；
• 当ItemID和TreeID不匹配时，始终通过tree.ItemID关联itemnames表，不要用TreeID直接关联。

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