如何编写SQL语句反转CTE中的指定层级字段值？

给定如下CTE结构及数据：

WITH cal(ID, NAME, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7) AS (
    VALUES 
        (1,'aaa','rt','fg','as','df',null,null,null),
        (2,'bbb','yt','zx',null,null,null,null,null)
)

需要将每条记录中非空的LVL系列字段值顺序反转，最终得到如下结果：

WITH cal(ID, NAME, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7) AS (
    VALUES 
        (1,'aaa','df','as','fg','rt',null,null,null),
        (2,'bbb','zx','yt',null,null,null,null,null)
)

以下提供两种不同场景下的实现方案：

方案一：使用数组函数（适用于PostgreSQL、BigQuery等支持数组的数据库） #

这种方法代码简洁，利用数组的过滤、反转特性快速实现需求：

WITH cal(ID, NAME, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7) AS (
    VALUES 
        (1,'aaa','rt','fg','as','df',null,null,null),
        (2,'bbb','yt','zx',null,null,null,null,null)
),
reversed_lvl AS (
    SELECT 
        ID,
        NAME,
        -- 1. 将LVL字段转为数组；2. 移除数组中的null；3. 反转数组
        ARRAY_REVERSE(ARRAY_REMOVE(ARRAY[LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7], NULL)) AS reversed_vals
    FROM cal
)
SELECT 
    ID,
    NAME,
    reversed_vals[1] AS LVL1,
    reversed_vals[2] AS LVL2,
    reversed_vals[3] AS LVL3,
    reversed_vals[4] AS LVL4,
    reversed_vals[5] AS LVL5,
    reversed_vals[6] AS LVL6,
    reversed_vals[7] AS LVL7
FROM reversed_lvl;

逻辑说明： #

• ARRAY[LVL1, ..., LVL7]：把水平排列的LVL字段转为垂直的数组
• ARRAY_REMOVE(..., NULL)：过滤掉数组中的空值
• ARRAY_REVERSE(...)：反转数组内元素的顺序
• 最后通过数组索引reversed_vals[N]将反转后的值映射回对应的LVL字段，数组长度不足的位置自动返回null

方案二：使用UNPIVOT+PIVOT（适用于SQL Server等不支持数组的传统数据库） #

通过将列转成行处理顺序，再转回列的方式实现反转：

WITH cal(ID, NAME, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7) AS (
    VALUES 
        (1,'aaa','rt','fg','as','df',null,null,null),
        (2,'bbb','yt','zx',null,null,null,null,null)
),
unpivoted AS (
    SELECT 
        ID,
        NAME,
        Value,
        -- 对每个ID的非空值，按原字段顺序倒序编号，得到反转后的位置
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CASE WHEN Value IS NOT NULL THEN 1 ELSE 2 END, ord DESC) AS new_ord
    FROM (
        SELECT 
            ID,
            NAME,
            LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7,
            1 AS ord1, 2 AS ord2, 3 AS ord3, 4 AS ord4, 5 AS ord5, 6 AS ord6, 7 AS ord7
        FROM cal
    ) t
    -- 将LVL字段拆成行记录
    UNPIVOT (
        Value FOR LvlCol IN (LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7)
    ) up
    -- 将顺序编号拆成行记录，与LVL字段匹配
    UNPIVOT (
        ord FOR OrdCol IN (ord1, ord2, ord3, ord4, ord5, ord6, ord7)
    ) up_ord
    WHERE LvlCol = 'LVL' + CAST(ord AS VARCHAR(10))
),
pivoted AS (
    -- 将处理后的行记录转回列，按新的顺序编号映射到LVL字段
    SELECT 
        ID,
        NAME,
        [1] AS LVL1,
        [2] AS LVL2,
        [3] AS LVL3,
        [4] AS LVL4,
        [5] AS LVL5,
        [6] AS LVL6,
        [7] AS LVL7
    FROM unpivoted
    PIVOT (
        MAX(Value) FOR new_ord IN ([1],[2],[3],[4],[5],[6],[7])
    ) p
)
SELECT * FROM pivoted;

逻辑说明： #

1. UNPIVOT：把每个LVL字段及其对应的顺序编号拆成行，方便处理顺序
2. ROW_NUMBER()：对每个ID的非空值，按原顺序倒序生成新的编号，这个编号就是反转后的字段位置
3. PIVOT：根据新编号将行记录转回列，得到反转后的LVL字段值

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