PostgreSQL表结构转换求助：实现年份维度的字段重组

针对你需要的表结构转换，这里提供两种可行的SQL方案：

方法一：使用unnest配合数组匹配列名与值 #

这种方法适合列数固定的场景，通过同步展开列名数组和对应的值数组，确保字段与数值一一对应，再通过条件聚合透视年份：

SELECT
    id,
    field,
    MAX(CASE WHEN year = 2000 THEN val END) AS value_2000,
    MAX(CASE WHEN year = 2001 THEN val END) AS value_2001
FROM
    your_table, -- 替换成你的实际表名
    unnest(ARRAY['column1', 'column2', 'column3']) AS field,
    unnest(ARRAY[column1, column2, column3]) AS val
GROUP BY id, field
ORDER BY id, field;

注意：列名数组和值数组的顺序必须完全一致，才能保证field和val正确匹配。

方法二：使用jsonb_each_text动态提取键值对 #

如果你的表列数较多，不想手动维护数组，可借助JSON函数自动拆解列名和对应值，适用性更广泛：

SELECT
    id,
    field,
    MAX(CASE WHEN year = 2000 THEN val::numeric END) AS value_2000,
    MAX(CASE WHEN year = 2001 THEN val::numeric END) AS value_2001
FROM
    your_table, -- 替换成你的实际表名
    jsonb_each_text(to_jsonb(your_table) - 'id' - 'year') AS j(field, val)
GROUP BY id, field
ORDER BY id, field;

这里to_jsonb(your_table)将整行转为JSON对象，- 'id' - 'year'排除不需要转换的字段，jsonb_each_text把剩余的键值对拆分成行，最后通过条件聚合得到目标结构。

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