postgre 动态行转列

postgresql crosstab(text source_sql, text category_sql) api 应用

建表脚本
分数表

 CREATE TABLE score (
"id" int8 NOT NULL,
"item_id" int8 NOT NULL,
"item_size_id" int8 DEFAULT (-1) NOT NULL,
"score" numeric(18,4) NOT NULL,
"modify_time" timestamp(6) NOT NULL,
"student_id" int4,
PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

学生表

CREATE TABLE student (
"id" int8 NOT NULL,
"name" varchar(50) COLLATE "default" NOT NULL,
"password" varchar(100) COLLATE "default",
"address" varchar(100) COLLATE "default",
"cellphone" varchar(50) COLLATE "default",
"married" int2 DEFAULT (0)::smallint NOT NULL,
PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)

;
1.简单查询

SELECT
    *
FROM
    crosstab (
        'SELECT 
        item_id as rowid,
        student_id as  category  ,
        score as  values
FROM
    score
',
    'select  id  as rowid from   student GROUP BY id order by 1'
    ) AS T (
        科目 int8,
        张三 VARCHAR,
        李四 VARCHAR,
        王五 VARCHAR
    )

as 里面 的列名必须提前定义，必须和student 表查询出来个数对应起来，如select id as rowid from student GROUP BY id order by 1 查询结果是
张三
李四
王五
三个结果，这里也要三个列对应，列名可以随便起

2.如果source_sql 里面包含额外的字段注意，比如

SELECT
    *
FROM
    crosstab (
        'SELECT 
        item_id as rowid,
        item_size_id,
        -- 添加额外字段 必须在第二个，也就是说source_sql 第一个和最后两个是固定的
        -- category   和 values 放在最后
        student_id as  category  ,
        score as  values
FROM
    score
',
        'select  id  as rowid from   student GROUP BY id order by 1'
    ) AS T (
        科目 int8,
        item_size_id int8, -- 添加额外字段对应列
        张三 VARCHAR,
        李四 VARCHAR,
        王五 VARCHAR
    )