select listagg(某列,',')within group(order by 某列主键 desc)from 某表;
详细:
1、有一表,表结构如下:
2、想把这张表的信息合并一下,即以ID 和CODE 为联合主键,分组合并,把CODENAE里信息合并到一行里;此处用到多行合并到一行,合并结果如下:
步骤:
1.建表:
create table testlisstagg ( id int,code int,codename varchar2(20));
2、插入数据
insert into testlisstagg (ID, CODE, CODENAME)values (1,1, 'A');
insert into testlisstagg (ID, CODE, CODENAME)values (1,2, 'B');
insert into testlisstagg (ID, CODE, CODENAME)values (1,2, 'C');
insert into testlisstagg (ID, CODE, CODENAME)values (2,1, 'A');
insert into testlisstagg (ID, CODE, CODENAME)values (2,1, 'B');
insert into testlisstagg (ID, CODE, CODENAME)values (3,2, 'C');
insert into testlisstagg (ID, CODE, CODENAME)values (3,2, 'A');
3、合并使用的代码:
(1)
select distinct a.id,
a.code,
(select listagg(codename, ',') within group(order by code desc)
from testlisstagg b where b.id=a.id and b.code=a.code) 合并后
from testlisstagg a order by 1,2;
(2) 简便的写法:
select b.id,b.code, listagg(codename, ',') within group(order by code desc)
from testlisstagg b group by b.id,b.code order by b.id,b.code