如何将层级数据转置为多列？求PrestoDB/SQL Server解决方案

PrestoDB 实现（非递归） #

由于Presto不支持递归CTE，我们通过多层自连接构建节点路径，再拆分路径为层级列。该方法可适配任意层级，只需根据实际最大层级扩展逻辑。

1. 构建节点完整路径 #

先通过多次自连接生成从根节点到每个子节点的路径数组：

WITH path_builder AS (
    -- 根节点下的第一层子节点
    SELECT 
        ARRAY[Parent] AS node_path,
        Child AS current_node
    FROM your_table
    WHERE Parent = 'PL-79'

    UNION ALL

    -- 第二层节点
    SELECT 
        array_append(p.node_path, t.Parent),
        t.Child
    FROM your_table t
    JOIN path_builder p ON t.Parent = p.current_node

    UNION ALL

    -- 第三层节点
    SELECT 
        array_append(p.node_path, t.Parent),
        t.Child
    FROM your_table t
    JOIN path_builder p ON t.Parent = p.current_node
    WHERE cardinality(p.node_path) = 2

    UNION ALL

    -- 第四层节点（可继续添加UNION ALL块适配更多层级）
    SELECT 
        array_append(p.node_path, t.Parent),
        t.Child
    FROM your_table t
    JOIN path_builder p ON t.Parent = p.current_node
    WHERE cardinality(p.node_path) = 3
)

2. 拆分路径为层级列 #

将路径数组元素提取为对应层级列，同时补充无后续子节点的根节点直接子节点：

SELECT
    node_path[1] AS lvl1,
    node_path[2] AS lvl2,
    CASE WHEN cardinality(node_path) >= 3 THEN node_path[3] ELSE '' END AS lvl3,
    CASE WHEN cardinality(node_path) >= 4 THEN node_path[4] ELSE '' END AS lvl4,
    CASE WHEN cardinality(node_path) >= 5 THEN node_path[5] ELSE '' END AS lvl5
    -- 按实际层级数继续扩展CASE语句
FROM path_builder

UNION ALL

-- 补充根节点下无后续子节点的记录
SELECT
    Parent AS lvl1,
    Child AS lvl2,
    '' AS lvl3,
    '' AS lvl4,
    '' AS lvl5
FROM your_table
WHERE Parent = 'PL-79'
AND Child NOT IN (SELECT Parent FROM your_table)

ORDER BY lvl1, lvl2, lvl3, lvl4;

SQL Server 实现（递归CTE） #

SQL Server支持递归CTE，可高效遍历任意层级的父子结构，写法更简洁：

WITH hierarchy_cte AS (
    -- 锚点：根节点的直接子节点
    SELECT
        Parent AS lvl1,
        Child AS lvl2,
        CAST(NULL AS VARCHAR(50)) AS lvl3,
        CAST(NULL AS VARCHAR(50)) AS lvl4,
        Child AS current_node,
        2 AS depth
    FROM your_table
    WHERE Parent = 'PL-79'

    UNION ALL

    -- 递归遍历后续层级
    SELECT
        h.lvl1,
        h.lvl2,
        CASE WHEN h.depth = 2 THEN t.Parent ELSE h.lvl3 END AS lvl3,
        CASE WHEN h.depth = 3 THEN t.Parent ELSE h.lvl4 END AS lvl4,
        t.Child AS current_node,
        h.depth + 1 AS depth
    FROM your_table t
    JOIN hierarchy_cte h ON t.Parent = h.current_node
)

-- 整理最终结果，包含所有层级路径
SELECT DISTINCT
    lvl1,
    lvl2,
    lvl3,
    lvl4
    -- 层级更多时继续添加对应的lvl列
FROM hierarchy_cte

UNION ALL

-- 补充根节点下无后续子节点的记录
SELECT
    Parent AS lvl1,
    Child AS lvl2,
    NULL AS lvl3,
    NULL AS lvl4
FROM your_table
WHERE Parent = 'PL-79'
AND Child NOT IN (SELECT Parent FROM your_table)

ORDER BY lvl1, lvl2, lvl3, lvl4;

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