使用array_agg实现 PG16 下面的行变列

郑全

postgres=# create table test(id int,v int);
CREATE TABLE
postgres=# insert into test values(1,1);
INSERT 0 1
postgres=# insert into test values(1,2);
INSERT 0 1
postgres=# insert into test values(1,3);
INSERT 0 1
postgres=# insert into test values(2,20);
INSERT 0 1
postgres=# insert into test values(2,21);
INSERT 0 1
postgres=# insert into test values(2,22);
INSERT 0 1
postgres=# insert into test values(3,31);
INSERT 0 1
postgres=# insert into test values(3,32);
INSERT 0 1
postgres=# insert into test values(3,33);
INSERT 0 1
postgres=# select * from test;
id | v  
----+----
  1 |  1
  1 |  2
  1 |  3
  2 | 20
  2 | 21
  2 | 22
  3 | 31
  3 | 32
  3 | 33
(9 rows)


postgres=# select id,array_agg(v) from test group by id;
id | array_agg  
----+------------
  3 | {31,32,33}
  2 | {20,21,22}
  1 | {1,2,3}
(3 rows)