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.
