如何用SQL将非NULL数据向左填充至空列？

需求明确：将每行Column_A至Column_F中的非NULL值按原有顺序左对齐，剩余位置填充NULL。SQL完全可以实现，CASE语句可行但绝非最优解，更简洁高效的方案依赖数据库的数组/集合类函数，以下分场景说明：

1. 优先方案：利用数组/JSON函数（推荐） #

不同数据库有对应的原生函数可快速收集非NULL值并按位置分配，逻辑直观、代码量少。

PostgreSQL 示例 #

通过array和array_remove函数提取非NULL值数组，再按索引取值：

SELECT
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[1] AS New_Col1,
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[2] AS New_Col2,
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[3] AS New_Col3,
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[4] AS New_Col4,
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[5] AS New_Col5,
  (array_remove(ARRAY[Column_A, Column_B, Column_C, Column_D, Column_E, Column_F], NULL))[6] AS New_Col6
FROM your_table;

MySQL 8.0+ 示例 #

借助JSON数组聚合函数实现：

SELECT
  JSON_EXTRACT(non_null_vals, '$[0]') AS New_Col1,
  JSON_EXTRACT(non_null_vals, '$[1]') AS New_Col2,
  JSON_EXTRACT(non_null_vals, '$[2]') AS New_Col3,
  JSON_EXTRACT(non_null_vals, '$[3]') AS New_Col4,
  JSON_EXTRACT(non_null_vals, '$[4]') AS New_Col5,
  JSON_EXTRACT(non_null_vals, '$[5]') AS New_Col6
FROM (
  SELECT
    JSON_ARRAYAGG(col) AS non_null_vals
  FROM (
    SELECT Column_A AS col FROM your_table
    UNION ALL SELECT Column_B AS col FROM your_table
    UNION ALL SELECT Column_C AS col FROM your_table
    UNION ALL SELECT Column_D AS col FROM your_table
    UNION ALL SELECT Column_E AS col FROM your_table
    UNION ALL SELECT Column_F AS col FROM your_table
  ) t
  WHERE col IS NOT NULL
  GROUP BY your_table.id -- 替换为你的表主键，确保按行聚合
) t2;

SQL Server 示例 #

使用STRING_AGG拼接非NULL值后拆分：

SELECT
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 6) AS New_Col1,
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 5) AS New_Col2,
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 4) AS New_Col3,
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 3) AS New_Col4,
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 2) AS New_Col5,
  PARSENAME(REPLACE(filtered_vals, ',', '.'), 1) AS New_Col6
FROM (
  SELECT
    STRING_AGG(CASE WHEN col IS NOT NULL THEN col END, ',') WITHIN GROUP (ORDER BY pos) AS filtered_vals
  FROM (
    SELECT Column_A AS col, 1 AS pos FROM your_table
    UNION ALL SELECT Column_B AS col, 2 AS pos FROM your_table
    UNION ALL SELECT Column_C AS col, 3 AS pos FROM your_table
    UNION ALL SELECT Column_D AS col, 4 AS pos FROM your_table
    UNION ALL SELECT Column_E AS col, 5 AS pos FROM your_table
    UNION ALL SELECT Column_F AS col, 6 AS pos FROM your_table
  ) t
  GROUP BY your_table.id -- 替换为表主键
) t2;

2. CASE语句方案（仅作兼容备选） #

CASE语句可以实现，但逻辑嵌套极深，维护成本高，仅适合列数极少的场景。比如第一列的判断逻辑：

SELECT
  CASE
    WHEN Column_A IS NOT NULL THEN Column_A
    WHEN Column_B IS NOT NULL THEN Column_B
    WHEN Column_C IS NOT NULL THEN Column_C
    WHEN Column_D IS NOT NULL THEN Column_D
    WHEN Column_E IS NOT NULL THEN Column_E
    ELSE Column_F
  END AS New_Col1,
  -- 第二列需要排除第一列已取的非NULL值，嵌套逻辑会指数级复杂
  CASE
    WHEN Column_A IS NOT NULL THEN
      CASE
        WHEN Column_B IS NOT NULL THEN Column_B
        WHEN Column_C IS NOT NULL THEN Column_C
        WHEN Column_D IS NOT NULL THEN Column_D
        WHEN Column_E IS NOT NULL THEN Column_E
        ELSE Column_F
      END
    WHEN Column_B IS NOT NULL THEN
      CASE
        WHEN Column_C IS NOT NULL THEN Column_C
        WHEN Column_D IS NOT NULL THEN Column_D
        WHEN Column_E IS NOT NULL THEN Column_E
        ELSE Column_F
      END
    -- 剩余列需继续嵌套，代码冗余且易出错
  END AS New_Col2,
  -- ... 后续列逻辑以此类推
FROM your_table;

总结 #

• 优先选择数组/JSON/字符串聚合类函数的方案，代码简洁、可读性强、维护成本低，不同数据库均有对应实现。
• CASE语句仅作为极端兼容场景的备选，不推荐用于3列以上的需求。

内容的提问来源于stack exchange，提问作者Abhiram Reddy Kotu