基于叶子节点转置层级categories表，节点作为第一列

原表结构 #

目标表结构 #

要求将原表转置为每行对应一个叶子节点的格式（已知最大层级为3）：

尝试的错误SQL #

用户尝试了以下查询，但无法获取父节点名称，仅能获取ID：

SELECT * FROM
(
  SELECT id, name ,parent_id, level l
  FROM categories
connect by prior parent_id = id
)
PIVOT
(
  max(id)  --pivot clause
  FOR l   --pivot_for_clause
  IN (1 parent_id_1, 2 parent_id_2, 3 parent_id_2)  --pivot_in_clause
)
;

问题分析 #

1. CONNECT BY方向错误，无法正确遍历层级关系；
2. 仅对ID进行了PIVOT处理，未同步提取父节点名称；
3. 未筛选叶子节点，导致结果包含非叶子节点数据。

正确解决方案 #

方法1：CASE分组聚合实现 #

WITH category_hierarchy AS (
    SELECT
        id,
        name,
        level AS node_level,
        CONNECT_BY_ROOT id AS leaf_id,
        CONNECT_BY_ROOT name AS leaf_name
    FROM categories
    WHERE CONNECT_BY_ISLEAF = 1 -- 筛选叶子节点
    CONNECT BY PRIOR parent_id = id -- 从叶子向上遍历父节点
)
SELECT
    leaf_id,
    leaf_name,
    MAX(CASE WHEN node_level = 2 THEN id END) AS parent_id_1,
    MAX(CASE WHEN node_level = 2 THEN name END) AS parent_name_1,
    MAX(CASE WHEN node_level = 3 THEN id END) AS parent_id_2,
    MAX(CASE WHEN node_level = 3 THEN name END) AS parent_name_2
FROM category_hierarchy
GROUP BY leaf_id, leaf_name
ORDER BY leaf_id;

方法2：PIVOT同时处理ID与名称 #

WITH hierarchy_data AS (
    SELECT
        CONNECT_BY_ROOT id AS leaf_id,
        CONNECT_BY_ROOT name AS leaf_name,
        level AS node_level,
        id AS node_id,
        name AS node_name
    FROM categories
    WHERE CONNECT_BY_ISLEAF = 1 -- 筛选叶子节点
    CONNECT BY PRIOR parent_id = id
)
SELECT
    leaf_id,
    leaf_name,
    parent_1_id AS parent_id_1,
    parent_1_name AS parent_name_1,
    parent_2_id AS parent_id_2,
    parent_2_name AS parent_name_2
FROM hierarchy_data
PIVOT (
    MAX(node_id) AS id, MAX(node_name) AS name
    FOR node_level IN (
        2 AS parent_1,
        3 AS parent_2
    )
);

说明 #

• CONNECT_BY_ISLEAF = 1确保只返回没有子节点的叶子节点；
• CONNECT_BY_ROOT获取当前叶子节点的原始ID和名称；
• 层级对应关系：叶子节点为level 1，直接父节点为level 2，祖父节点为level 3，对应目标表的parent_id_1和parent_id_2。

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