오라클 문자열 합치기 WM_CONCAT, LISTAGG, XMLAGG, LEAD

오라클에서  WM_CONCAT 을 사용 했을때,, 정렬이 너무 맘대로 나와서..

쓰기가 좀 그랫었는데..

 

예제를 보자.. 

SELECT A.GRP, WM_CONCAT(A.CD) AS CD
FROM 
(
    SELECT 'A01' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'A01' AS GRP, '20' AS CD FROM DUAL
    UNION ALL
    SELECT 'A01' AS GRP, '30' AS CD FROM DUAL
    UNION ALL
    SELECT 'A02' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'B01' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'B01' AS GRP, '20' AS CD FROM DUAL
) A
GROUP BY A.GRP
;

 

 

이렇게 GRP 그룹 A01에 CD코드가 10, 30, 20 으로 정렬이 자기 맘대로 나오는 걸 볼수가 있다.

구분자도 , 콤마로 고정이고, ^^

 

WM_CONCAT은 12버전에서 드디어 삭제됨. ㅋㅋ 

 

 

 

 

 

​

​

오라클 11G 버전 이상이면 LISTAGG 함수로 원하는 것을 쪽 뽑아낼수 있다.

구분자도 줄수 있고, 정렬도 할수 있다.

​

SELECT A.GRP, LISTAGG(A.CD,':') WITHIN GROUP(ORDER BY A.CD) AS NAMES
FROM 
(
    SELECT 'A01' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'A01' AS GRP, '20' AS CD FROM DUAL
    UNION ALL
    SELECT 'A01' AS GRP, '30' AS CD FROM DUAL
    UNION ALL
    SELECT 'A02' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'B01' AS GRP, '10' AS CD FROM DUAL
    UNION ALL
    SELECT 'B01' AS GRP, '20' AS CD FROM DUAL
) A
GROUP BY A.GRP
;

 

이렇게 정렬도 하고 구분자도  : 요렇게 원하는 케릭터로... 지정해서 쓸수 있음..

 

합치는 문자열이 4000바이트 이상이면 오류가 난다.

ORA-01489

 

on overflower truncate '...'   --> 12버전에서 생긴..  넘치믄 짜르고 "..." 문자열 표시함.. 근본적인 해결은 안됨. ㅎ

 

 

-- 2016.04.07 역시나.. 오라클 버전 10g 에선 정렬이 안되는데.. 써야 할일이 있다... 열심히 구글링을 하여 써먹을 수 있는

XMLAGG 문서만드는거랑 .XMLELEMENT 엘레멘터. 요걸로.. 원하는 결과값을 얻을 수 있었다.

SELECT A.GRP, SUBSTR(XMLAGG(XMLELEMENT(COL, ',', A.CD) ORDER BY A.CD).EXTRACT('//text()').GETSTRINGVAL(), 2) AS CD
 FROM 
(
SELECT 'A01' AS GRP, '30' AS CD FROM DUAL
UNION ALL
SELECT 'A01' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'A01' AS GRP, '20' AS CD FROM DUAL
UNION ALL
SELECT 'A02' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'B01' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'B01' AS GRP, '20' AS CD FROM DUAL
) A
GROUP BY A.GRP

결과값

 

 

크핫... ㅎ 

 

 

SELECT A.GRP, SUBSTR(XMLAGG(XMLELEMENT(COL, ',', A.CD) ORDER BY A.CD).EXTRACT('//text()').GETSTRINGVAL(), 2) AS CD
 FROM 
(
SELECT 'A01' AS GRP, '30' AS CD FROM DUAL
UNION ALL
SELECT 'A01' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'A01' AS GRP, '20' AS CD FROM DUAL
UNION ALL
SELECT 'A02' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'B01' AS GRP, '10' AS CD FROM DUAL
UNION ALL
SELECT 'B01' AS GRP, '20' AS CD FROM DUAL
) A
GROUP BY A.GRP

 

 

GETSTRINGVAL() 대신 GETCLOBVAL()을 쓰면 4000바이트 이상 사용 할수도 있다.

12버전 이상에선 데이터 처리를 더 늘이수도 있다.