如何在PostgreSQL中将同一ID的多行数据转置为带年份后缀的单行？

方法一：条件聚合（推荐，无需额外扩展） #

如果你的年份范围是已知的（比如只有2023、2024），直接用CASE配合聚合函数就能快速实现。假设你的表名为volume_records，字段为ID、Year、Volume1、Volume2，SQL语句如下：

SELECT
    ID,
    -- 提取2023年的Volume1和Volume2
    MAX(CASE WHEN Year = 2023 THEN Volume1 END) AS Volume1_2023,
    MAX(CASE WHEN Year = 2023 THEN Volume2 END) AS Volume2_2023,
    -- 提取2024年的Volume1和Volume2
    MAX(CASE WHEN Year = 2024 THEN Volume1 END) AS Volume1_2024,
    MAX(CASE WHEN Year = 2024 THEN Volume2 END) AS Volume2_2024
FROM volume_records
GROUP BY ID
ORDER BY ID;

说明 #

• 因为同一ID+Year只有一行记录，用MAX（或MIN/SUM）可以精准取出对应年份的数值，没有对应年份的记录会显示NULL。
• 若有更多年份，只需复制对应的CASE语句块修改年份即可。

方法二：动态生成列（适配年份不固定的场景） #

如果年份数量不确定或经常变动，手动写CASE太繁琐，可以用PL/pgSQL动态生成SQL语句：

DO $$
DECLARE
    year_col_defs text;
BEGIN
    -- 自动生成所有年份的Volume1/Volume2列的CASE语句
    SELECT string_agg(
        format(
            'MAX(CASE WHEN Year = %s THEN Volume1 END) AS Volume1_%s, MAX(CASE WHEN Year = %s THEN Volume2 END) AS Volume2_%s',
            y.year, y.year, y.year, y.year
        ),
        ', '
    ) INTO year_col_defs
    FROM (SELECT DISTINCT Year FROM volume_records ORDER BY Year) y;

    -- 执行动态拼接的查询语句
    EXECUTE format(
        'SELECT ID, %s FROM volume_records GROUP BY ID ORDER BY ID',
        year_col_defs
    );
END $$;

说明 #

• 这段代码会先从表中提取所有不重复的年份，自动拼接出对应年份的列定义，再执行最终查询。
• 如果需要将结果保存到新表，可以把EXECUTE语句改成CREATE TABLE new_table AS ...的形式。

方法三：使用crosstab交叉表扩展 #

如果涉及更复杂的交叉表需求，可以使用PostgreSQL的tablefunc扩展（需先安装）：

1. 先安装扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写交叉表查询：

SELECT
    ID,
    Volume1_2023, Volume2_2023,
    Volume1_2024, Volume2_2024
FROM crosstab(
    -- 第一步：将数据转换为长格式（ID + 类型 + 数值）
    'SELECT ID, Year || ''_1'' AS col_type, Volume1 FROM volume_records UNION ALL
     SELECT ID, Year || ''_2'' AS col_type, Volume2 FROM volume_records ORDER BY 1, 2',
    -- 第二步：定义需要转换的列类型
    'SELECT DISTINCT Year || ''_1'' FROM volume_records UNION ALL
     SELECT DISTINCT Year || ''_2'' FROM volume_records ORDER BY 1'
) AS ct_result(
    ID text,
    Volume1_2023 numeric, Volume2_2023 numeric,
    Volume1_2024 numeric, Volume2_2024 numeric
);

说明 #

• 该方法需要先将Volume1和Volume2合并为统一的类型字段（如2023_1对应Volume1_2023），再通过crosstab转换为宽表。
• 若年份不固定，同样需要动态生成列定义，实现逻辑类似方法二。

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