层级表中基于NULL值反转合并非空列的SQL实现需求

嘿，这个需求我太熟了！本质上就是把每行的非空值按从右到左的顺序重新排列，左对齐填充到原列位置，右边补NULL对吧？我给你分几种常见数据库的实现方案，你可以根据自己用的数据库选：

核心思路 #

先把每行的col1-col4按从右到左的顺序（col4→col3→col2→col1）收集非空值，然后把这些值依次分配到新的col1、col2、col3、col4，没有值的位置填NULL。

1. PostgreSQL 实现 #

PostgreSQL的数组函数非常适合这个场景，一行子查询就能搞定：

SELECT
    dim,
    reversed_non_null[1] AS col1,
    reversed_non_null[2] AS col2,
    reversed_non_null[3] AS col3,
    reversed_non_null[4] AS col4
FROM (
    SELECT
        dim,
        -- 按col4→col3→col2→col1的顺序生成数组，过滤掉NULL
        array_remove(ARRAY[col4, col3, col2, col1], NULL) AS reversed_non_null
    FROM your_table
) sub;

解释：内层查询直接按从右到左的列顺序生成数组，去掉NULL后就得到了我们需要的有序非空值列表；外层查询按数组下标取对应位置的值，没有值的下标会自动返回NULL，完美匹配需求。

2. MySQL 实现 #

MySQL没有原生数组支持，我们可以用列转行+分组拼接的方式实现：

SELECT
    dim,
    -- 分割拼接后的字符串，取第1个值
    SUBSTRING_INDEX(reversed_vals, ',', 1) AS col1,
    -- 非空值≥2个时取第2个，否则返回NULL
    CASE WHEN non_null_count >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(reversed_vals, ',', 2), ',', -1) ELSE NULL END AS col2,
    CASE WHEN non_null_count >= 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(reversed_vals, ',', 3), ',', -1) ELSE NULL END AS col3,
    CASE WHEN non_null_count >= 4 THEN SUBSTRING_INDEX(reversed_vals, ',', -1) ELSE NULL END AS col4
FROM (
    SELECT
        dim,
        -- 按col4到col1的顺序拼接非空值
        GROUP_CONCAT(val ORDER BY pos DESC SEPARATOR ',') AS reversed_vals,
        COUNT(val) AS non_null_count
    FROM (
        -- 把列转行，给每个列标记位置（col4是最高位，对应pos=4）
        SELECT dim, col1 AS val, 1 AS pos FROM your_table
        UNION ALL
        SELECT dim, col2 AS val, 2 AS pos FROM your_table
        UNION ALL
        SELECT dim, col3 AS val, 3 AS pos FROM your_table
        UNION ALL
        SELECT dim, col4 AS val, 4 AS pos FROM your_table
    ) unpivoted
    WHERE val IS NOT NULL
    GROUP BY dim
) sub;

解释：先把每列转成一行并标记位置（pos越大对应原表越靠右的列）；然后按dim分组，用GROUP_CONCAT按pos从大到小的顺序拼接非空值；最后用SUBSTRING_INDEX分割字符串，把值分配到对应列，不足的位置返回NULL。

3. SQL Server 实现 #

如果是SQL Server 2022及以上版本，支持数组函数，写法和PostgreSQL类似：

SELECT
    dim,
    reversed_non_null[0] AS col1, -- SQL Server数组下标从0开始
    reversed_non_null[1] AS col2,
    reversed_non_null[2] AS col3,
    reversed_non_null[3] AS col4
FROM (
    SELECT
        dim,
        -- 按col4→col3→col2→col1的顺序生成数组，过滤NULL
        ARRAY(SELECT val FROM (VALUES(col4), (col3), (col2), (col1)) AS v(val) WHERE val IS NOT NULL) AS reversed_non_null
    FROM your_table
) sub;

如果是旧版本（2017+支持STRING_AGG），可以用JSON来处理：

SELECT
    dim,
    JSON_VALUE(reversed_json, '$[0]') AS col1,
    JSON_VALUE(reversed_json, '$[1]') AS col2,
    JSON_VALUE(reversed_json, '$[2]') AS col3,
    JSON_VALUE(reversed_json, '$[3]') AS col4
FROM (
    SELECT
        dim,
        -- 把拼接后的字符串转成JSON数组
        '["' + REPLACE(STRING_AGG(val, '","') WITHIN GROUP (ORDER BY pos DESC), '""', '"') + '"]' AS reversed_json
    FROM (
        SELECT dim, col1 AS val, 1 AS pos FROM your_table
        UNION ALL
        SELECT dim, col2 AS val, 2 AS pos FROM your_table
        UNION ALL
        SELECT dim, col3 AS val, 3 AS pos FROM your_table
        UNION ALL
        SELECT dim, col4 AS val, 4 AS pos FROM your_table
    ) unpivoted
    WHERE val IS NOT NULL
    GROUP BY dim
) sub;

你可以把上面的your_table替换成你实际的表名，测试一下就能得到你想要的结果啦！

备注：内容来源于stack exchange，提问作者user124123