Greenplum行列转换操作

行转列

一般行转列过程需要排序才能保障列的顺序正常，所以会使用order by

select  apply_no , 
split_part(string_agg(flow_step||'|'||flow_time,'|'ORDER BY flow_id),'|',1) insure1, 
split_part(string_agg(flow_step||'|'||flow_time,'|'ORDER BY flow_id),'|',2) insure1_time ,
split_part(string_agg(flow_step||'|'||flow_time,'|'ORDER BY flow_id),'|',3) insure2, 
split_part(string_agg(flow_step||'|'||flow_time,'|'ORDER BY flow_id),'|',4) insure2_time ,
 from
(
SELECT s.id AS flow_id,
       s.apply_no, 
       s.real_title AS flow_step, 
       s.updated_at AS flow_time
  FROM  apply_flow_steps s 
 WHERE  step in ('insure1','insure2') 
) ss
group by  apply_no 

列转行就更加简单了

select id,regexp_split_to_table(str,E'\\|') str from test1