分组数据聚合

数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

一、创建测试数据：

 

create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');

 

二、多行数据的聚合

2.1 wm_concat

 

SELECT ID,
       to_char(wm_concat('['||NAME||ID||']')) colname
       FROM TEST GROUP BY ID

2.2 listagg

 

 

SELECT ID,
       listagg(NAME,'$') WITHIN GROUP(ORDER BY NAME) OVER(PARTITION BY ID) colname
       FROM TEST

SELECT ID,
       listagg(NAME,'$') WITHIN GROUP(ORDER BY NAME) colname
       FROM TEST
       GROUP BY ID

 

详细语法见oracle官网：点击打开链接