PostgreSQL行转列实现咨询：学生成绩表结构转换

不需要Java中间件，PostgreSQL原生支持实现 #

PostgreSQL提供了多种方式直接完成行转列的需求，无需依赖中间件处理，以下是具体实现方案：

方案一：静态列（科目固定时） #

如果已知所有科目（比如示例中的Maths和Physics），可以用FILTER子句或CASE表达式实现，写法简洁直观：

使用FILTER子句（PostgreSQL 9.4+支持）

SELECT
    学生ID,
    MAX(分数) FILTER (WHERE 科目 = 'Maths') AS Maths,
    MAX(分数) FILTER (WHERE 科目 = 'Physics') AS Physics
FROM 成绩表
GROUP BY 学生ID;

使用CASE表达式（兼容更早版本）

SELECT
    学生ID,
    MAX(CASE WHEN 科目 = 'Maths' THEN 分数 END) AS Maths,
    MAX(CASE WHEN 科目 = 'Physics' THEN 分数 END) AS Physics
FROM 成绩表
GROUP BY 学生ID;

说明：使用MAX聚合函数是因为每个学生对应单个科目的成绩唯一，聚合后会直接取对应分数；若学生无某科目成绩，结果会返回NULL（对应示例中的空单元格）。

方案二：动态列（科目不固定时） #

如果科目数量不确定、可能动态变化，可以通过以下两种方式实现：

方法1：使用crosstab函数（需安装tablefunc扩展）

首先启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行交叉表查询：

SELECT *
FROM crosstab(
    -- 源数据查询，需按学生ID、科目排序
    'SELECT 学生ID, 科目, 分数 FROM 成绩表 ORDER BY 1, 2',
    -- 定义目标列的科目列表
    'SELECT DISTINCT 科目 FROM 成绩表 ORDER BY 1'
) AS ct(学生ID INT, Maths INT, Physics INT);

注意：ct后的列定义需要和科目列表一一对应，若要完全动态生成列名，需结合动态SQL。

方法2：动态SQL生成（PL/pgSQL）

通过自定义函数动态生成行转列的SQL语句，适配任意科目数量：

CREATE OR REPLACE FUNCTION 动态生成成绩列()
RETURNS SETOF RECORD AS $$
DECLARE
    col_defs TEXT;
BEGIN
    -- 动态生成每个科目的列表达式
    SELECT string_agg(
        format('MAX(分数) FILTER (WHERE 科目 = %L) AS %I', 科目, 科目),
        ', '
    ) INTO col_defs
    FROM (SELECT DISTINCT 科目 FROM 成绩表 ORDER BY 科目) AS sub;

    -- 执行动态生成的查询
    RETURN QUERY EXECUTE format(
        'SELECT 学生ID, %s FROM 成绩表 GROUP BY 学生ID',
        col_defs
    );
END;
$$ LANGUAGE plpgsql;

调用函数时需指定返回结构（例如SELECT * FROM 动态生成成绩列() AS t(学生ID INT, Maths INT, Physics INT)），也可进一步优化函数使其自动适配列结构。

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