PostgreSQL如何拆分并映射两个数组列为独立列

现有数据表包含ID、Key、Value三列，其中Key和Value为逗号分隔的数组形式，原始数据示例：

需要将每个Key映射为独立列，对应填充Value值，缺失则留空，最终目标结构：

方法一：SQL实现（以PostgreSQL为例） #

核心思路是先拆分数组为行数据，再通过透视转换为宽表：

-- 第一步：拆分逗号分隔的键值对为多行数据
WITH key_value_rows AS (
    SELECT 
        ID,
        UNNEST(string_to_array(Key, ',')) AS key_col,
        UNNEST(string_to_array(Value, ',')) AS value_col
    FROM your_table
)
-- 第二步：将行数据透视成宽表
SELECT 
    ID,
    MAX(CASE WHEN key_col = 'a' THEN value_col END) AS a,
    MAX(CASE WHEN key_col = 'b' THEN value_col END) AS b,
    MAX(CASE WHEN key_col = 'c' THEN value_col END) AS c,
    MAX(CASE WHEN key_col = 'd' THEN value_col END) AS d
FROM key_value_rows
GROUP BY ID
ORDER BY ID;

说明：string_to_array把字符串转成数组，UNNEST拆分数组为多行；用CASE配合MAX聚合函数实现透视，缺失的键值会自动显示为空。

方法二：Python Pandas实现 #

通过拆分数组、展开行数据，再透视得到目标结构：

import pandas as pd

# 构造原始数据
data = {
    'ID': [1,2,3,4],
    'Key': ['a,b,c,d', 'b,c', 'a,c', 'b,c,d'],
    'Value': ['1,2,3,4', '7,8', '0,9', '3,7,1']
}
df = pd.DataFrame(data)

# 拆分Key和Value为列表，再展开成多行
df['Key'] = df['Key'].str.split(',')
df['Value'] = df['Value'].str.split(',')
df_exploded = df.explode(['Key', 'Value'], ignore_index=True)

# 透视成宽表，移除多余的列索引名称
result = df_exploded.pivot(index='ID', columns='Key', values='Value').reset_index()
result.columns.name = None

# 可选：将NaN转为空字符串
result = result.fillna('')
print(result)

运行后输出结果与目标表完全一致。

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