oracle listagg支持,PostgreSQL行列转换(兼容oracle listagg)

oracle11g开始支持的listagg函数替代了wmconcat来实现行列转换的功能。

listagg函数的用法：

oracle行列转换例子：

—建表https://www.cndba.cn/foucus/article/3929https://www.cndba.cn/foucus/article/3929

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，实现同样的功能。https://www.cndba.cn/foucus/article/3929

https://www.cndba.cn/foucus/article/3929

pg行列转换例子：

—建表https://www.cndba.cn/foucus/article/3929

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表示。

https://www.cndba.cn/foucus/article/3929

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表示。

https://www.cndba.cn/foucus/article/3929

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值https://www.cndba.cn/foucus/article/3929

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)

版权声明：本文为博主原创文章，未经博主允许不得转载。