ORACLE LISTAGG函数

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');
commit;

--11g2版本之前采用WM_CONTCAT来连接
SELECT ID, WM_CONCAT(NAME) NAME FROM TEST GROUP BY ID;
--11g2版本后,采用ListAgg
SELECT ID,
       LISTAGG(NAME, ',') WITHIN GROUP(ORDER BY NAME DESC) RANK --GROUP指分组后的排序
  FROM TEST
 GROUP BY ID;

SELECT ID,
       NAME,
       LISTAGG(NAME, ',') WITHIN GROUP(ORDER BY ID DESC) OVER(PARTITION BY ID) RANK 
       --不采用GROUP,要采用PARTITION分组
  FROM TEST;