我建议将当前数据转换为排除NULL值的“未透视状态”，同时在混合中添加一个行号。然后，一旦可用，然后使用primary_key和行号将该中间结果重新透视为所需的最终格式：

WITH UNPIV AS (
    SELECT PRIMARY_KEY, 'column_1' AS COLUMN_NAME, column_1 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_1) AS RN
    FROM your_table
    WHERE column_1 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_2' AS COLUMN_NAME, column_2 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_2) AS RN
    FROM your_table
    WHERE column_2 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_3' AS COLUMN_NAME, column_3 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_3) AS RN
    FROM your_table
    WHERE column_3 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_4' AS COLUMN_NAME, column_4 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_4) AS RN
    FROM your_table
    WHERE column_4 IS NOT NULL
    )
SELECT PRIMARY_KEY, 
       MAX(CASE WHEN COLUMN_NAME = 'column_1' THEN COLUMN_VALUE END) AS COLUMN_1,
       MAX(CASE WHEN COLUMN_NAME = 'column_2' THEN COLUMN_VALUE END) AS COLUMN_2,
       MAX(CASE WHEN COLUMN_NAME = 'column_3' THEN COLUMN_VALUE END) AS COLUMN_3,
       MAX(CASE WHEN COLUMN_NAME = 'column_4' THEN COLUMN_VALUE END) AS COLUMN_4
FROM UNPIV
GROUP BY PRIMARY_KEY, RN
ORDER BY PRIMARY_KEY, RN

字符串
| 列_1| COLUMN_2| COLUMN_3|列_4| COLUMN_4 |
| --|--|--|--| ------------ |
| 一个|二个|三个|四个| 4 |
| 五个|六个| * 空 | 空 *| null |
fiddle
请注意，这些行中的值的排列由row_number（）计算确定。因此，不能保证值将重新对齐到它们所源自的同一行中。