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
;