Oracle 多行转列方法
Creating a comma-separated list in SQL

For some reason I can never understand, one of the most-asked Oracle questions on the Web goes something like this:

It is self-contained, as no PL/SQL functions or object types are required, and The results are ordered.

The following example illustrates the technique using the SCOTT demo table "emp":2

SELECT deptno      , LTRIM(SYS_CONNECT_BY_PATH(ename,','))FROM   ( SELECT deptno              , ename              , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS seq          FROM   emp ) WHERE  connect_by_isleaf = 1CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno START WITH seq = 1;    DEPTNO CONCATENATED---------- --------------------------------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.

Another approach involves harnessing the dark power of XML:3

SELECT deptno     , RTRIM       ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()')       , ',' ) AS concatenatedFROM   empGROUP BY deptno;    DEPTNO CONCATENATED---------- ---------------------------        10 CLARK,KING,MILLER        20 ADAMS,FORD,JONES,SCOTT,SMITH        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.