Greenplum 字符串行列转换函数

Greenplum 字符串行列转换函数

hank=> create table test (id bigint,str text)  DISTRIBUTED BY (id);

CREATE TABLE

hank=> insert into test values(1,'dazuiba');

INSERT 0 1

hank=> insert into test values(1,'apple');

INSERT 0 1

hank=> insert into test values(1,'pump');

INSERT 0 1

hank=> insert into test values(2,'orange');

INSERT 0 1

hank=> select id,string_agg(str,'|')  from test group by id; 

 id |     string_agg     

----+--------------------

  1 | dazuiba|apple|pump

  2 | orange

字符串列转行函数

create table test1 as select id,string_agg(str,'|') str from test group by id; 

hank=> select id,regexp_split_to_table(str,E'\\|') str from test1; 

 id | str 

----+-----------------------

  1 | dazuiba

  1 | apple

  1 | pump

  2 | orange