PostgreSQL 實(shí)現(xiàn)將多行合并轉(zhuǎn)為列

CREATE TABLE Student_score(姓名 varchar, 課程 varchar, 分?jǐn)?shù) int);
INSERT INTO Student_score VALUES('張三','數(shù)學(xué)',83);
INSERT INTO Student_score VALUES('張三','物理',93);
INSERT INTO Student_score VALUES('張三','語文',80);
INSERT INTO Student_score VALUES('李四','語文',74);
INSERT INTO Student_score VALUES('李四','數(shù)學(xué)',84);
INSERT INTO Student_score VALUES('李四','物理',94);
select 姓名
  ,sum(case 課程 when '數(shù)學(xué)' then 分?jǐn)?shù) end) as 數(shù)學(xué)
  ,sum(case 課程 when '物理' then 分?jǐn)?shù) end) as 物理
  ,sum(case 課程 when '語文' then 分?jǐn)?shù) end) as 語文
from Student_score
GROUP BY 1

CREATE TABLE Student_grade(姓名 varchar, 課程 varchar, 等級 varchar);
INSERT INTO Student_grade VALUES('張三','數(shù)學(xué)','優(yōu)');
INSERT INTO Student_grade VALUES('張三','物理','良');
INSERT INTO Student_grade VALUES('張三','語文','及格');
INSERT INTO Student_grade VALUES('李四','語文','及格');
INSERT INTO Student_grade VALUES('李四','數(shù)學(xué)','良');
INSERT INTO Student_grade VALUES('李四','物理','優(yōu)');

  ,string_agg((case 課程 when '數(shù)學(xué)' then 等級 end),'') as 數(shù)學(xué)
  ,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 姓名,課程,分?jǐn)?shù)
  from Student_score
  order by 1,2')
AS (姓名 varchar, 數(shù)學(xué) 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 : 轉(zhuǎn)換為列名的字段
value_field : 轉(zhuǎn)換為值的字段
*/
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;--獲取值字段的數(shù)據(jù)類型
  
  v_sql='select '||row_name;
  IF value_type in ('numeric','int8','int4','int')--判斷值字段是否是數(shù)值型 
    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;