PostgreSQL 实现将多行合并转为列

CREATE TABLE Student_score(姓名 varchar , 课程 varchar , 分数 int );

INSERT INTO Student_score VALUES ( '张三' , '数学' ,83);

INSERT INTO Student_score VALUES ( '张三' , '物理' ,93);

INSERT INTO Student_score VALUES ( '张三' , '语文' ,80);

INSERT INTO Student_score VALUES ( '李四' , '语文' ,74);

INSERT INTO Student_score VALUES ( '李四' , '数学' ,84);

INSERT INTO Student_score VALUES ( '李四' , '物理' ,94);

select 姓名

   , sum ( case 课程 when '数学' then 分数 end ) as 数学

   , sum ( case 课程 when '物理' then 分数 end ) as 物理

   , sum ( case 课程 when '语文' then 分数 end ) as 语文

from Student_score

GROUP BY 1

CREATE TABLE Student_grade(姓名 varchar , 课程 varchar , 等级 varchar );

INSERT INTO Student_grade VALUES ( '张三' , '数学' , '优' );

INSERT INTO Student_grade VALUES ( '张三' , '物理' , '良' );

INSERT INTO Student_grade VALUES ( '张三' , '语文' , '及格' );

INSERT INTO Student_grade VALUES ( '李四' , '语文' , '及格' );

INSERT INTO Student_grade VALUES ( '李四' , '数学' , '良' );

INSERT INTO Student_grade VALUES ( '李四' , '物理' , '优' );

   ,string_agg(( case 课程 when '数学' then 等级 end ), '' ) as 数学

   ,string_agg(( case 课程 when '物理' then 等级 end ), '' ) as 物理

   ,string_agg(( case 课程 when '语文' then 等级 end ), '' ) as 语文

from Student_grade

GROUP BY 1

SELECT *

FROM crosstab(

  'select row_name,cat,value

   from table

   order by 1,2' )

AS (row_name type, category_1 type, category_2 type, category_3 type, ...);

SELECT *

FROM crosstab(

  'select 姓名,课程,分数

   from Student_score

   order by 1,2' )

AS (姓名 varchar , 数学 int , 物理 int , 语文 int );

CREATE or REPLACE FUNCTION

long_to_wide(

table_name VARCHAR ,

row_name VARCHAR ,

cat VARCHAR ,

value_field VARCHAR )

returns void as

$$

/*

table_name : 表名

row_name : 行名字段

cat : 转换为列名的字段

value_field : 转换为值的字段

*/

DECLARE v_sql text;

arow record;

value_type VARCHAR ;

BEGIN

   v_sql= '

   drop table if exists temp_table;

   CREATE TABLE temp_table as

   SELECT distinct ' ||cat|| ' as col from ' ||table_name|| '

   order by ' ||cat;

   execute v_sql;

   v_sql= '

   SELECT t.typname AS type

   FROM pg_class c

   ,pg_attribute a

   ,pg_type t

   WHERE c.relname = lower(' '' ||table_name|| '' ')

   and a.attnum > 0

   and a.attrelid = c.oid

   and a.atttypid = t.oid

   and a.attname=' '' ||value_field|| '' '

   ORDER BY a.attnum

   ' ;

   execute v_sql into value_type; --获取值字段的数据类型

   v_sql= 'select ' ||row_name;

   IF value_type in ( 'numeric' , 'int8' , 'int4' , 'int' ) --判断值字段是否是数值型

     THEN

     FOR arow in ( SELECT col FROM temp_table) loop

     v_sql=v_sql|| '

         ,sum(case ' ||cat|| ' when ' '' ||arow.col|| '' ' then ' ||value_field|| ' else 0 end) ' ||cat|| '_' ||arow.col;

     end loop;

   ELSE

     FOR arow in ( SELECT col FROM temp_table) loop

     v_sql=v_sql|| '

     ,string_agg((case ' ||cat|| ' when ' '' ||arow.col|| '' ' then ' ||value_field|| ' else ' '' ' end),' '' ') ' ||cat|| '_' ||arow.col;

     end loop;

   END IF;

   v_sql= '

         drop table if exists ' ||table_name|| '_wide;

         CREATE TABLE ' ||table_name|| '_wide as

         ' ||v_sql|| '

         from ' ||table_name|| '

         group by ' ||row_name|| ';

         drop table if exists temp_table

         ' ;

   execute v_sql;

end ;

$$ LANGUAGE plpgsql;