postgres数据表行转列的方法

 

行转列

原数据:

select * from tth1; a | b | c -------+-------+---- 考生z | 科目1 | 33 考生l | 科目1 | 33 考生z | 科目2 | 43 考生z | 科目3 | 63 考生l | 科目2 | 87 考生l | 科目3 | 99 (6 rows) 

 

结果：

select * from testrowcol() as a(c1 varchar,c2 text, c3 text,c4 text); c1 | c2 | c3 | c4 -------+----+----+---- 考生l | 87 | 99 | 33 考生z | 43 | 63 | 33 (2 rows)

 

 

 

表格式：

CREATE TABLE tth1 ( a character varying(20), b character varying(20), c character varying(20) );  

 

 

 

函数：

 

CREATE FUNCTION testrowcol() RETURNS SETOF record LANGUAGE plpgsql AS $$ declare r record; ff varchar:='SELECT A,'; begin for r in select distinct B from tth1 loop ff:=ff || 'max(case when B='''||r.B || '''' || ' then C ' || ' end ) as "'||r.B||'",'; RAISE NOTICE '%', ff; end loop; ff:=substr(ff,1,length(ff)-1) || ' from tth1 group by A'; RAISE NOTICE '%', ff; FOR r IN execute ff Loop RETURN NEXT r; END LOOP; end; $$;