오라클에서 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 으로 정렬이 자기 맘대로 나오는 걸 볼수가 있다.
구분자도 , 콤마로 고정이고, ^^
오라클 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
;
이렇게 정렬도 하고 구분자도 : 요렇게 원하는 케릭터로… 지정해서 쓸수 있음..
