如何将4列数据转换为层级结构的单列？UNPIVOT实现疑问

需求说明 #

需要将4列的扁平数据转换为树形层级结构的单列输出，无需保留示例中的LEVEL标识，只需保证层级顺序正确。

原始数据 #

Col1        Col2        Col3              Col4
YP HEVEA    YP HEVEA    OTHER CREDITORS   L1220001 - OTHER CREDITORS
YP HEVEA    YP HEVEA    PREPAYMENTS       A1323004 - PREPAYMENTS OTHERS
YP HEVEA    YP HEVEA    PREPAYMENTS       A2349032 - PREPAYMENTS NEW

期望输出（仅展示顺序，无需LEVEL列） #

YP HEVEA
YP HEVEA
OTHER CREDITORS
L1220001 - OTHER CREDITORS
PREPAYMENTS
A1323004 - PREPAYMENTS OTHERS
A2349032 - PREPAYMENTS NEW

测试数据SQL #

SELECT 'YP HEVEA'                   Col1,
       'YP HEVEA'                   Col2,
       'OTHER CREDITORS'            Col3,
       'L1220001 - OTHER CREDITORS' Col4
FROM   DUAL
UNION
SELECT 'YP HEVEA'                      Col1,
       'YP HEVEA'                      Col2,
       'PREPAYMENTS'                   Col3,
       'A1323004 - PREPAYMENTS OTHERS' Col4
FROM   DUAL
UNION
SELECT 'YP HEVEA'                   Col1,
       'YP HEVEA'                   Col2,
       'PREPAYMENTS'                Col3,
       'A2349032 - PREPAYMENTS NEW' Col4
FROM   DUAL  

解决方案 #

可以结合窗口函数与UNION/UNION ALL实现层级排序和去重，确保层级顺序正确且重复的上层节点仅出现一次：

WITH base_data AS (
    SELECT * FROM (
        SELECT 'YP HEVEA'                   Col1,
               'YP HEVEA'                   Col2,
               'OTHER CREDITORS'            Col3,
               'L1220001 - OTHER CREDITORS' Col4
        FROM   DUAL
        UNION
        SELECT 'YP HEVEA'                      Col1,
               'YP HEVEA'                      Col2,
               'PREPAYMENTS'                   Col3,
               'A1323004 - PREPAYMENTS OTHERS' Col4
        FROM   DUAL
        UNION
        SELECT 'YP HEVEA'                   Col1,
               'YP HEVEA'                   Col2,
               'PREPAYMENTS'                Col3,
               'A2349032 - PREPAYMENTS NEW' Col4
        FROM   DUAL
    )
),
hierarchy_data AS (
    -- 提取第一、二层（去重）
    SELECT 1 AS sort_order, Col1 AS item FROM base_data UNION
    SELECT 2 AS sort_order, Col2 AS item FROM base_data UNION
    -- 提取第三层（按Col3分组去重，分配排序值）
    SELECT 2 + DENSE_RANK() OVER(ORDER BY Col3) AS sort_order, Col3 AS item 
    FROM base_data GROUP BY Col3 UNION ALL
    -- 提取第四层，对应第三层的分组排序
    SELECT 3 + DENSE_RANK() OVER(PARTITION BY Col3 ORDER BY Col3) AS sort_order, Col4 AS item 
    FROM base_data
)
SELECT item
FROM hierarchy_data
ORDER BY sort_order;

逻辑说明 #

1. base_data：封装测试数据集；
2. hierarchy_data：
   • 第一、二层用UNION去重，固定排序值；
   • 第三层通过DENSE_RANK()给不同分组分配排序值，保证分支顺序；
   • 第四层关联第三层的分组排序值，确保子节点跟随对应父节点；
3. 最后按排序值输出，得到树形层级的单列结果。

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