Rows를 grouping 하여 columns로 표현하기

@ROWS데이타

GROUP_ID     SEQNO  CODE_CD      CODEVALUE

0001   00001  CD01   00001

0001   00001  CD02   00001

0001   00001  CD03   00001

0001   00002  CD01   00002

0001   00002  CD02   00001

0001   00002  CD03   00001

0001   00003  CD01   00001

0001   00003  CD02   00002

0001   00003  CD03   00001

0001   00004  CD01   00001

0001   00004  CD02   00001

0001   00004  CD03   00002

==> @columns 데이타로 표현

GROUP_ID     SEQNO  VAL

0001   00001  CD0100001CD0200001CD0300001

0001   00002  CD0100002CD0200001CD0300001

0001   00003  CD0100001CD0200002CD0300001

0001   00004  CD0100001CD0200001CD0300002

XMLType Methods를 이용한다.

예)

WITH TMP_ROWSTOCOLUMNS AS  
(SELECT '0001' AS GROUP_ID, '00001' SEQNO,'CD01' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00001' SEQNO,'CD02' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00001' SEQNO,'CD03' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00002' SEQNO,'CD01' CODE_CD, '00002' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00002' SEQNO,'CD02' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00002' SEQNO,'CD03' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00003' SEQNO,'CD01' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00003' SEQNO,'CD02' CODE_CD, '00002' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00003' SEQNO,'CD03' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00004' SEQNO,'CD01' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00004' SEQNO,'CD02' CODE_CD, '00001' CODEVALUE FROMDUAL UNION
SELECT '0001' AS GROUP_ID, '00004' SEQNO,'CD03' CODE_CD, '00002' CODEVALUE FROMDUAL)
 SELECT  GROUP_ID,SEQNO
      , SUBSTR(XMLAGG(XMLELEMENT(A,'' ||NVL(CODE_CD || CODEVALUE,'')) ORDER BY SEQNO,CODE_CD).EXTRACT('//text()'), 1) ASVAL
  FROM  TMP_ROWSTOCOLUMNS A
GROUP BY A.GROUP_ID,SEQNO ORDER BY 2

참고) oracle document : http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb04cre.htm