oracle11g开始支持的listagg函数替代了wmconcat来实现行列转换的功能。
listagg函数的用法:
oracle行列转换例子:
–建表
SQL> create table b (id number,name varchar2(20));
Table created.
–插入数据
insert into b values(1,'zhang');
insert into b values(1,'wang');
insert into b values(2,'li');
insert into b values(2,'zhao');
–行列转换
SQL> select id,LISTAGG(name,',') within group(order by name desc) as bb from b group by id;
ID
----------
BB
--------------------------------------------------------------------------------
1
zhang,wang
2
zhao,li
pg中是没有listagg和wmconcat函数的,那么我们要怎么在pg中如何实现同样的功能呢?pg中的string_agg函数可以用来代替listagg,实现同样的功能。
pg行列转换例子:
–建表
bill=# create table tbl1 (gid int, val text, ts timestamp default clock_timestamp());
CREATE TABLE
–插入数据
bill=# insert into tbl1 values (1,'a'),(1,'b'),(1,null),(2,'test'),(2,'a""b"c'),(3,'fw');
INSERT 0 6
bill=# select * from tbl1;
gid | val | ts
-----+--------+----------------------------
1 | a | 2020-01-05 14:08:42.995435
1 | b | 2020-01-05 14:08:42.995636
1 | | 2020-01-05 14:08:42.995642
2 | test | 2020-01-05 14:08:42.995645
2 | a""b"c | 2020-01-05 14:08:42.995648
3 | fw | 2020-01-05 14:08:42.995717
(6 rows)
–行列转换
逆向聚合,双引号作为quote字符,转义文本内的双引号,空值使用NULL表示。
bill=# select gid, string_agg(coalesce('"'||replace(val,'"','\"')||'"','NULL'),',' order by ts desc) from tbl1 group by gid;
gid | string_agg
-----+--------------------
1 | NULL,"b","a"
2 | "a\"\"b\"c","test"
3 | "fw"
(3 rows)
正向聚合,双引号作为quote字符,转义文本内的双引号,空值使用NULL表示。
bill=# select gid, string_agg(coalesce('"'||replace(val,'"','\"')||'"','NULL'),',' order by ts) from tbl1 group by gid;
gid | string_agg
-----+--------------------
1 | "a","b",NULL
2 | "test","a\"\"b\"c"
3 | "fw"
(3 rows)
正向聚合,不使用QUOTE,直接去除NULL值
bill=# select gid, string_agg(val,',' order by ts) from tbl1 group by gid;
gid | string_agg
-----+-------------
1 | a,b
2 | test,a""b"c
3 | fw
(3 rows)