[DB] 오라클(oracle) 여러로우의 값을 하나의 컬럼으로 바꾸는 방법

출처 : http://amnesis.tistory.com/4

GBN	VAL
A	001
A	002
A	003
A	004
A	005
A	011
B	007
B	004
B	005
B	010
C	011
C	001
C	002
C	003

이런 내용이 있을때 gbn으로 그룹핑을 하고 val의 내용을 하나의 컬럼으로 받고 싶다고 해보자
예를 들어

GBN	VAL
A	001,002,003,004,005,011
B	004,005,007,010
C	001,002,003,011

이런식으로..^^

대부분의 경우 역시 프로그램에서 해결하거나, decode 구분에 rownum이나 rank등을 사용하여 처리 했을 것이다.

하지만 프로그램에서 처리 할 경우는 디비에서 보다 느려지게되고,
decode 구분으로 처리 할 겨우는 deocde로 지정되어 있는 갯수만큼만 나열되게 된다.

이때 쓸 수 있는 좋은 방법이 xmlagg와 xmlelement 함수다

xmlelement는 주어진 태그로 값을 감싸 하나의 xml 엘리먼트를 만들어 주고
xmlagg는 xml 문서를 만들어주는 함수이다.


select  gbn,
          xmlelement(a,val) val
  from test

의 결과는

GBN	VAL
A	<A>001</A>
A	<A>002</A>
A	<A>003</A>
A	<A>004</A>
A	<A>005</A>
A	<A>011</A>
B	<A>007</A>
B	<A>004</A>
B	<A>005</A>
B	<A>010</A>
C	<A>011</A>
C	<A>001</A>
C	<A>002</A>
C	<A>003</A>

이렇게 나오게 된다. 
xmlelement(a,val) --> 첫번째 항은 태그명이 되며 두번째 항은 태그내의 값이 된다.
                                첫번째 항을 소문자로 적어도 대문자로 태그가 만들어진다
                                따로 문자열 표시를 하지 않기 때문에 예약어의 경우 에러가 난다.
                                그때는 "로 감싸주면 된다.

이것을 

select  gbn,
           xmlagg(xmlelement(a,val)) val
  from test
group by gbn

이렇게 xmlagg로 한번 감싸 준다. xmlagg는 그룹함수로 해당 내용을 xml 문서로 만들어 준다
결과는 

GBN	VAL
A	<A>001</A><A>002</A><A>003</A><A>005</A><A>011</A><A>004</A>
B	<A>007</A><A>004</A><A>010</A><A>005</A>
C	<A>011</A><A>003</A><A>001</A><A>002</A>

요렇게 된다.

여기서 xml함수를 하나 더써서 태그 빼고 값만 가져온다
더불어 xmlagg 내의 내용을 val 로 order by  해준다

select  gbn,
          xmlagg(xmlelement(a,val) order by val).extract('//text()') val
  from test
group by gbn  

GBN	VAL
A	001002003004005011
B	004005007010
C	001002003011

val 값을 서로 구분하기 위해 값을 가져올때 미리 ','를 붙여 보자

select  gbn,
           xmlagg(xmlelement(a,',' || val) order by val).extract('//text()') val
  from test
group by gbn

GBN	VAL
A	,001,002,003,004,005,011
B	,004,005,007,010
C	,001,002,003,011

마지막으로 맨 앞의 콤마 하나를 제거해준다

select  gbn,
          substr(xmlagg(xmlelement(a,',' || val) order by val).extract('//text()'), 2) val
  from test
group by gbn  

GBN	VAL
A	001,002,003,004,005,011
B	004,005,007,010
C	001,002,003,011

완성~~~ ^^


ps. xmlagg나 xmlelement를 사용하여 나온 결과는 xml형을 가지게 됩니다. 이걸 substr을 쓰면 문자열형식으로 바뀌는데, substr을 사용하지 않고 문자열 형식으로 받고 싶을 때는
xmlagg(xmlelement(a,',' || val) order by val).extract('//text()')
뒤에 가볍게
.getStringVal()
요거 하나 붙여주세요~
xmlagg(xmlelement(a,',' || val) order by val).extract('//text()').getStringVal()


#########################################################################################

내용추가(2011.07.01)

출처 : http://www.oracleclub.com/article/21720

-- 8i -- SELECT num      , MIN(DECODE(rn,1,''''||data||''''))     || MIN(DECODE(rn,2,','''||data||''''))     || MIN(DECODE(rn,3,','''||data||''''))   FROM (SELECT num, data              , ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn           FROM t         )  GROUP BY num ; -- 9i -- SELECT num      , SUBSTR(MAX(SYS_CONNECT_BY_PATH(''''||data||'''',',')),2) data   FROM (SELECT num, data              , ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn           FROM t         )  START WITH rn = 1  CONNECT BY PRIOR num = num         AND PRIOR rn = rn - 1  GROUP BY num ; -- 10G -- SELECT num      , REPLACE(SUBSTR(data,2),'@','''') data   FROM ( SELECT num, XMLAGG(XMLELEMENT(x,',@'||data||'@') ORDER BY data).EXTRACT('//text()').GetStringVal() data   FROM t  GROUP BY num ) ; 10G XML 함수에서는 따옴표가 ' 로 표시되는 관계로 부득이하게 @ 로 대체해서 다시한번 처리했습니다.