[ORACLE]오라클 문자열 합치기 기능 정리

오라클 문자열 합치기 기능 정리

 

아래 내용은 개인적으로 참고용 기록을 남기고자 정리하였습니다.

모든 건 참고 url에서 가져왔으므로 해당 url들을 보는 것을 추천합니다.

 

참고url

http://www.gurubee.net/lecture/2676

http://tyboss.tistory.com/22

wm_concat : http://blog.naver.com/PostView.nhn?blogId=jinh2004&logNo=80092526183

xmlagg, xmlelement, extract : http://amnesis.tistory.com/4

 

 

출처 : http://www.gurubee.net/lecture/2676

• 9i : SUBSTR(XMLAgg(XMLELEMENT(x, ',', empno) ORDER BY empno).Extract('//text()'), 2) "9i"
• 10g : WM_CONCAT(empno) "10g"
• 11g : LISTAGG(empno, ',') WITHIN GROUP(ORDER BY empno) "11g"

 

SELECT deptno

     , SUBSTR(XMLAgg(XMLELEMENT(x, ',', empno) ORDER BY empno).Extract('//text()'), 2) "9i"

     , WM_CONCAT(empno) "10g"

     , LISTAGG(empno, ',') WITHIN GROUP(ORDER BY empno) "11g"

  FROM emp

 GROUP BY deptno

 ORDER BY deptno

 

기존에 개인적으로 문자열 합치기 처리했던 쿼리

Sys_connect_path 를 사용하였음

select substr(max(lpad(level, 5, ',') || sys_connect_by_path(reg_name, ',')), 7)

from (

    select row_number() over (order by reg_name) as rn

        , a.*

    from board2 a

) connect by prior rn=rn-1

 

1.     Row_number을 이용하여 정렬과 일련번호 구함

2.     Connect by prior 에 rn을 이용하여 계청철

3.     Sys_connect_by_path 를 이용하여 , 기준으로 나오게 함

4.     Lpad(level, 5, ‘,’) 를 통해 ,,,31 식으로 나오게 함

5.     max값을 구함으로써 3 || 4 의 결과의 max값을 구함 => level이 제일 높은 값중 한값이 선택됨

6.     맨 앞의 ,,,, 같은 불필요한 문자를 substring함

  

XMLAgg, xmlelement 이용

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

select SUBSTR(XMLAgg(XMLELEMENT(x, ',', reg_name) ORDER BY reg_name).Extract('//text()'), 2)

from board2

• xmlelement(x, ',', reg_name) : reg_name을 <x> 기준으로 xml문자열로 변환
• xmlagg : xml값을 한줄로 만듬
• extract('//text()') : xml값 중 text 항목만 추려냄
• substr로 불필요한 , 제거
• xmlagg로 가져오는 형식은 xml형식이라고 함, 이를 substr 처리 시 문자열 형식이 된다고 함, 그냥 바로 가져올 경우에는 .getStringVal()을 붙여서 사용한다고 함 

wm_concat

출처 : http://blog.naver.com/PostView.nhn?blogId=jinh2004&logNo=80092526183

• 비공개된 function 이라고 함
• Distinct, keep, over 가능하다고 함

일반적으로 집계함수로 사용시

select wm_concat(reg_name)

from board2

 

over 를 이용하여 분석함수로 사용시

select wm_concat(reg_name) over (partition by reg_name order by seq)

from board2

 

keep를 사용

select wm_concat(reg_name) keep (dense_Rank last order by seq)

from board2

 

distinct 사용

select wm_concat(distinct reg_name)

from board2

 

LISTAGG(reg_name, ',') WITHIN GROUP(ORDER BY reg_name)

11g 환경이 아니라 테스트 못해봄

 

참고url

http://www.gurubee.net/lecture/2676

http://tyboss.tistory.com/22

wm_concat : http://blog.naver.com/PostView.nhn?blogId=jinh2004&logNo=80092526183

xmlagg, xmlelement, extract : http://amnesis.tistory.com/4