세로로된 데이터 가로로 출력하기.

출처: http://zent.tistory.com/64

with EMP as (
SELECT 10 AS DEPTNO, 'CLARK  ' AS ENAME FROM DUAL
UNION
SELECT 10 AS DEPTNO, 'KING   ' AS ENAME FROM DUAL
UNION
SELECT 10 AS DEPTNO, 'MILLER ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'SMITH  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'ADAMS  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'FORD   ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'SCOTT  ' AS ENAME FROM DUAL
UNION
SELECT 20 AS DEPTNO, 'JONES  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'ALLEN  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'BLAKE  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'MARTIN ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'JAMES  ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'TURNER ' AS ENAME FROM DUAL
UNION
SELECT 30 AS DEPTNO, 'WARD   ' AS ENAME FROM DUAL
) 
SELECT DEPTNO, LTRIM(SYS_CONNECT_BY_PATH(ENAME, ','),',') ENAME
FROM (
SELECT DEPTNO
, ENAME
, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM
, COUNT(*) OVER (PARTITION BY DEPTNO) CNT
FROM EMP
)
WHERE LEVEL = CNT
START WITH RNUM = 1
CONNECT BY PRIOR DEPTNO = DEPTNO 
AND PRIOR RNUM = RNUM - 1

결과

DEPTNO   ENAME 
--------    ----------------------
10             MILLER ,KING   ,CLARK   
20             JONES  ,FORD   ,ADAMS  ,SCOTT  ,SMITH   
30             ALLEN  ,BLAKE  ,JAMES  ,MARTIN ,TURNER ,WARD    

* 다른방법
select deptno, XMLAgg(XMLElement("nm", ename||',')).EXTRACT('//text()').GetStringVal() ename 
from emp
group by deptno

결과

DEPTNO ENAME 
--------  --------------------
10           CLARK  ,KING   ,MILLER , 
20           ADAMS  ,SMITH  ,SCOTT  ,FORD   ,JONES  , 
30           ALLEN  ,JAMES  ,TURNER ,WARD   ,MARTIN ,BLAKE  , 


* Decode 를 사용하는 방법 (컬럼갯수에 따라 달라진다 번거로움..)

SELECT DEPTNO,
            MAX(DECODE(RID, 1, ENAME)) EMP1,
            MAX(DECODE(RID, 2, ENAME)) EMP2,
            MAX(DECODE(RID, 3, ENAME)) EMP3,
            MAX(DECODE(RID, 4, ENAME)) EMP4,
            MAX(DECODE(RID, 5, ENAME)) EMP5,
            MAX(DECODE(RID, 6, ENAME)) EMP6
     FROM ( 
          SELECT DEPTNO, 
                 ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ENAME) RID, 
                ENAME
          FROM EMP
          )
     GROUP BY DEPTNO

결과

DEPTNO EMP1   EMP2   EMP3      EMP4     EMP5    EMP6 
-------- ------   -----    -----      -----      -----     -----
10         CLARK   KING   MILLER   
20         ADAMS  FORD   JONES   SCOTT    SMITH 
30         ALLEN   BLAKE JAMES   MARTIN   TURNER WARD