[Oracle] 오라클 피벗(1) - LISTAGG, WM_CONCAT

[Oracle] 오라클 피벗(1) - LISTAGG, WM_CONCAT

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ 오라클 피벗, listagg, wm_concat (피벗, 첫번째) ] 입니다. :)  

 

여러 Row 값을  하나의 컬럼으로  돌려서 가져오고 싶은때가 있다. 

이때 , 등 구분자를 통해 가져오는 방법이다.  (ex 갓대희, 김동수, 김치맨 )

(오라클에서 피벗턴 가능 하다 => 세로정렬을 가로정렬로 전환)

 

다음과 같은 로그인 히스토리가 있다고 가정하자.

 

 

▶ 임시 데이터 생성 

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
SELECT * FROM TEMPLOGINLOG;

 - 샘플 데이터

 

 

 

▶ 방법1. WM_CONCAT (정렬불가)

 

 - 기본문법 : wm_concat(컬럼명)

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
--1번 방법 :  정렬불가한 피벗
SELECT ID, WM_CONCAT(NAME) AS NAME
FROM TEMPLOGINLOG
GROUP BY ID;

 - 쿼리결과

쿼리 실행 후 =>>>>>>>>

 

 

▶ 방법2. LISTAGG (정렬 가능)

 - 정렬가능

 - Oracle 11g 이상만 사용가능

 - VARCHAR2형이기 때문에 4000을 넘는경우 잘린다.

 

※ 기본문법

 - listagg (컬럼명, '구분기호') within group (order by 정렬기준컬럼)

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- 2번 방법 : Oracle 11g 이상 사용가능

-- 장점이라고 보면 (1)정렬가능, (2) 구분자 변경 가능
SELECT
  ID,
  LISTAGG(NAME,',') WITHIN GROUP(ORDER BY NAME) AS NAME
FROM TEMPLOGINLOG
GROUP BY ID;

 - 쿼리결과

쿼리 실행 후 =>>>>>>>>

▶ 방법3.(Oracle 11g 미만인 경우) 정렬 하고 싶을때 다음과 같이 사용할 수 도 있다.

 

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

 

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
--2번 방법 : 정렬가능
SELECT
  ID,
  SUBSTR(XMLAGG(XMLELEMENT(A,',' || NAME) ORDER BY NAME).EXTRACT('//text()'), 2) NAME
FROM TEMPLOGINLOG
GROUP BY ID;

 - 쿼리결과

쿼리 실행 후 =>>>>>>>>

 

 

※ 원리 분석

 

▶ 3번 방법 해당 쿼리 원리 (1)

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- xmlelement는 주어진 태그로 값을 감싸 하나의 xml 엘리먼트를 만들어 준다.
-- 첫번째 값은 태그명, 두번째 값은 태그내의 값이 된다.
-- 첫번째 값을 소문자로 적어도 대문자로 태그가 만들어진다
-- 따로 문자열 표시를 하지 않기 때문에 예약어의 경우 에러가 난다. 이때 "로 감싸주면 해결 가능.
-- xmlagg나 xmlelement를 사용하여 나온 결과는 xml형을 가지게 됩니다. 이걸 substr을 쓰면 문자열형식으로 바뀌는데
-- , substr을 사용하지 않고 문자열 형식으로 받고 싶을 때는 .getStringVal()을 사용하면 된다.

 

SELECT  ID, XMLELEMENT(A, NAME).getStringVal() AS NAME
FROM    TEMPLOGINLOG
;

 - 쿼리결과

쿼리 실행 후 =>>>>>>>>

 

▶ 3번 방법 해당 쿼리 원리 (2)

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- XMLAGG는 그룹함수, xml 문서를 만들어주는 함수.

SELECT  ID, XMLAGG(XMLELEMENT(A, NAME)).getStringVal() AS NAME
FROM    TEMPLOGINLOG
GROUP   BY ID
;

- 쿼리결과

쿼리 실행전             쿼리 실행 후

 

▶ 3번 방법 해당 쿼리 원리 (3)

 

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- EXTRACT 를 사용하여 테스트만 가져오고, 정렬해주기
SELECT  ID, XMLAGG(XMLELEMENT(A, NAME) ORDER BY NAME).EXTRACT('//text()').getStringVal() AS NAME
FROM    TEMPLOGINLOG
GROUP   BY ID
;

- 쿼리결과

쿼리 실행전             쿼리 실행 후

 

▶ 3번 방법 해당 쿼리 원리 (4) 

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- 구분자 , 추가해주기
SELECT  ID, XMLAGG(XMLELEMENT(A,',' || NAME) ORDER BY NAME).EXTRACT('//text()').getStringVal() AS NAME
FROM    TEMPLOGINLOG
GROUP   BY ID
;

- 쿼리결과

쿼리 실행전             쿼리 실행 후

 

 

▶ 3번 방법 해당 쿼리 원리 (5) 

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- 맨앞 ,(콤마) 제거
SELECT  ID, substr(XMLAGG(XMLELEMENT(A,',' || NAME) ORDER BY NAME).EXTRACT('//text()'), 2) AS NAME
FROM    TEMPLOGINLOG
GROUP   BY ID
;

- 쿼리결과

쿼리 실행전             쿼리 실행 후

 

▶ 3번 방법 해당 쿼리 원리 (4), (5)로 진행 하는 경우 다음과 같은 에러가 발생할 수도 있다.

ORA-19011: 문자열 버퍼가 너무 작음

19011. 00000 -  "Character string buffer too small" 

*Cause:    The string result asked for is too big to return back

*Action:   Get the result as a lob instead

기본적으로 오라클 12몇버전이지... 오라클 12 특정 버전 미만인 경우

String Type은 4000Bytes 제한이 걸리게 된다.

그렇기 때문에 다음과 같이 쿼리를 변경하여 해결 하도록 하였다.

3-4, 3-5에서는 마지막 형 변환시 String으로 변환하였는데 Clob형으로 변환하여 사용 해본다.

ex)

WITH TEMPLOGINLOG AS (
SELECT 'MOBILE' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '갓대희' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김동수' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '김치맨' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '원더우먼' AS NAME FROM DUAL
UNION ALL SELECT 'PC' AS ID, '세바스찬' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '갓당근' AS NAME FROM DUAL
UNION ALL SELECT 'MOBILE' AS ID, '굠댕이' AS NAME FROM DUAL
)
-- 구분자 , 추가해주기
SELECT  ID, XMLAGG(XMLELEMENT(A,',' || NAME) ORDER BY NAME).EXTRACT('//text()').getCLOBval() AS NAME
FROM    TEMPLOGINLOG
;

업무를 하면서 사용해 보니.... 이렇다.

편하게 사용하려면 정렬 여부에 따라 LISTAGG, WM_CONCAT 을 사용하면 된다.

다만 4000Bytes를 넘어가면 사용 불가.

결국 데이터 오바가 발생할 요지가 있다면 XMLAGG 를 사용하게 된다.

다른 의견이 있으시면 언제든 글남겨주세요.

---

관련글 

2017/08/03 - [4. Database/4_3 Oracle 기본] - [Oracle] 오라클 피벗(2) - PIVOT, UNPIVOT, 행을 열로, 열을 행으로

---