2022-09-06 · STORY

오라클 LISTAGG, 중복제거 하는 방법

WITH TBL_TEST AS (
    SELECT '1' AS IDX, 'test' AS COL FROM DUAL UNION ALL
    SELECT '1', 'test'  FROM DUAL UNION ALL 
    SELECT '2', 'xx'  FROM DUAL UNION ALL
    SELECT '2', 'yy'  FROM DUAL UNION ALL
    SELECT '3', 'zzz'  FROM DUAL UNION ALL
    SELECT '3', 'zzz'  FROM DUAL UNION ALL
    SELECT '3', 'z'  FROM DUAL
)
SELECT A.IDX
     , REGEXP_REPLACE(LISTAGG(A.COL, ',') WITHIN GROUP (ORDER BY A.IDX), '([^,]+)(,\1)+', '\1') AS COL
FROM TBL_TEST A
WHERE 1 = 1
GROUP BY A.IDX
;