[ORACLE] 여러행을 하나로 합치는 방법

출처 : http://amnesis.tistory.com/4 달바라기(착한악마)님의 블로그

아래 소개할 방법은 일반적으로 행과 열을 바꾸는 pivot 이나

sys_connect_by_path , connect_by_prior 를 사용하는것과는 약간 다르지만 유용합니다.

행을 모두 여러개의 열로 바꾸는것이 아니라

행을 모두 그룹지어서 하나의 칸에 몽땅 구분자로 넣는 방법입니다. ^-^

[주의] 마지막의 //text() 는 반드시 소문자이어야 하군요!!

-----------------------------------------------------------------------------------

 1. 기본

-----------------------------------------------------------------------------------

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()

-----------------------------------------------------------------------------------

 2. 추가

-----------------------------------------------------------------------------------

전에 xml을 사용하여 sys_conect_by 보다 효율적으로 여러로우의 컬럼 값을 합하는 방법을 알려드린 적이 있다.
헌데 여기에 약간에 문제가 있는데 값의 중복과 널값 배제문제다.

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

만약 위와 같은 데이터를 갖고 있는 테이블에서
SELECT gbn,
             substr(xmlagg(xmlelement(a,',' || val) order by val).extract('//text()'), 2) val
   FROM lis.testt
 GROUP BY gbn
위와 같은 쿼리를 돌리면 결과가

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

위와 같이 나온다
이때 A,B의 경우 각각 001, 004가 중복으로 나오며 C의 경우는 널 값 때문에 마지막에 콤마가 하나 더 붙게 된다

extract('//text()')라는 구문을 쓸 수 있다는 것에 힌트를 얻어 xml에서 중복배제나 널값을 뺄 수 있는 함수가 있는지 찾아 보았으나 찾지 못했다
(혹시 아시는 분은 힌트 좀...ㅡㅜ)

위의 내용을 해결하기 위해는 몇가지 트릭을 써야 한다

extract('//text()')에서 extract('/a/text()') 라고 쓰게 되면 a 태그로 된 것들만 결과로 가져온다.

해서
select gbn,
          substr(xmlagg(
                          sys.XMLType.createXML(
                                     '<'||flag||'>,' || val||'</'||flag||'>'
                                     ) order by val).extract('/a1/text()'), 2) val
from (select gbn,
                  val,
                  nvl2(val, 'a' || row_number() over (partition by gbn, val order by val),
                               'a2')  as flag
        from lis.testt)
group by gbn

이런식으로 처리가 가능하다
결과를 보면

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

위의 내용을 설명하면 우선 생성되는 xml의 태그 값을 마음대로 지정할 수 있기 위해
xmlelement를 sys.XMLType.createXML로 바꾸었다

그리고 원하는 값만을 가져오기 위해 extract('/a1/text()')로 'a1'을 추가 하였고,
원하는 데이터 상태를 만들기 위해 프롬절 아래 서브쿼리를 썼다

중요한 부분은
nvl2(val, 'a' || row_number() over (partition by gbn, val order by val), 'a') flag
요부분인데

nvl2함수를 사용하여 값이 널일 때는 태그 값이 되는 flag에 'a'를 넘기고

row_number() over (partition by gbn, val order by val)
를 사용하여 중복된 값에 일련번호를 주고 숫자는 xml태그 값으로 사용될 수 없기에 앞에 'a'를 하나 붙여 주었다.

위와 같은 방법을 응용하여 여러가지 조건으로 xmlagg 내부에서 완성되는 xml에서 원하는 값을 뽑아 낼 수 있다.

주의 )  xmlagg 완성된 자료형은 xml형입니다. 당연히 CLAB 처럼 길이 제한이 없습니다.
          하지만 substr 같은 문자열 함수를 쓰는 즉시 varchar(4000)의 제한이 걸립니다
          만약 그 이상의 길이로 데이터를 받고 싶다면 앞에 콤마는 프로그램에서
          제거 하셔야 합니다.

-------------------------------------------------------------------------------------------

출처 : http://amnesis.tistory.com/4 달바라기(착한악마)님의 블로그