【转】PostgreSQL多行变一行

TAGS

PGSQL多行聚合,PGSQL多行合并为一行,PGSQL多行合并

《PostgreSQL 多行变一行》
《PostgreSQL官方文档-聚集函数》

postgres=# \d book2
                              Table "public.book2"
 Column |       Type        |                     Modifiers                      
--------+-------------------+----------------------------------------------------
 id     | integer           | not null default nextval('book2_id_seq'::regclass)
 name   | character varying | 
 tag    | character varying |
postgres=# select * from book2;
 id | name | tag 
----+------+-----
  1 | java | aa
  1 | java | bb
  1 | java | cc
  2 | C++  | dd
  2 | C++  | ee
(5 rows)

需要的效果:

 name | string_agg 
------+------------
 C++  | dd,ee
 java | aa,bb,cc
(2 rows)

方法1:string_agg(字段名,分隔符)

postgres=# select name,string_agg(tag,',') from book2 group by name;
 name | string_agg 
------+------------
 C++  | dd,ee
 java | aa,bb,cc
(2 rows)

方法2:array_agg(字段名)

postgres=# select name,array_agg(tag) from tb3 group by name;
 name | array_agg  
------+------------
 c++  | {dd,ee}
 java | {aa,bb,cc}
(2 rows)
postgres=# select name,array_to_string(array_agg(tag),',') from tb3 group by name;
 name | array_to_string 
------+-----------------
 c++  | dd,ee