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;