[펌] 여러로우의 값을 하나의 컬럼으로 바꾸는 방법[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)의 제한이 걸립니다
          만약 그 이상의 길이로 데이터를 받고 싶다면 앞에 콤마는 프로그램에서
          제거 하셔야 합니다.

ps. 두번째 글을 올리면서 혹시나 싶어 검색을 해보니 제 글이 출처도 제대로 표시 안된
      채로 오타가 있는 그대로 이리저리 떠돌고 있더군요..ㅡㅜ
      제 블로그에 글도 얼마 안되지만 시간내서 쓰는 사람의 정성을 생각해서 출처 표시는
      해주시면 감사하겠습니다


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