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  valuesFROM    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  valuesFROM    score',        'select  id  as rowid from   student GROUP BY id order by 1'    ) AS T (        科目 int8,        item_size_id int8, -- 添加额外字段对应列        张三 VARCHAR,        李四 VARCHAR,        王五 VARCHAR    )