달바라기
[오라클] 여러로우의 값을 하나의 컬럼으로 바꾸는 방법[2] 본문
전에 xml을 사용하여 sys_conect_by 보다 효율적으로 여러로우의 컬럼 값을 합하는 방법을 알려드린 적이 있다.
헌데 여기에 약간에 문제가 있는데 값의 중복과 널값 배제문제다.
위와 같이 나온다
이때 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
이런식으로 처리가 가능하다
결과를 보면
위의 내용을 설명하면 우선 생성되는 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. 두번째 글을 올리면서 혹시나 싶어 검색을 해보니 제 글이 출처도 제대로 표시 안된
채로 오타가 있는 그대로 이리저리 떠돌고 있더군요..ㅡㅜ
제 블로그에 글도 얼마 안되지만 시간내서 쓰는 사람의 정성을 생각해서 출처 표시는
해주시면 감사하겠습니다.
헌데 여기에 약간에 문제가 있는데 값의 중복과 널값 배제문제다.
| 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. 두번째 글을 올리면서 혹시나 싶어 검색을 해보니 제 글이 출처도 제대로 표시 안된
채로 오타가 있는 그대로 이리저리 떠돌고 있더군요..ㅡㅜ
제 블로그에 글도 얼마 안되지만 시간내서 쓰는 사람의 정성을 생각해서 출처 표시는
해주시면 감사하겠습니다.