SELECT A.SN,A.ORGN_NM
, REGEXP_REPLACE(LISTAGG( A.BSNS_REG_NO, ',') WITHIN GROUP(ORDER BY A.SBJT_NO)
, '([^,]+)(,\1)*(,|$)', '\1\3') AS BSNSR_REG_NO
FROM
(SELECT 1 AS SN, 'AA001' AS SBJT_NO ,'ㅁㅁㅁ' AS ORGN_NM, '123-12-324' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'AA001' AS SBJT_NO ,'ㅁㅁㅁ' AS ORGN_NM, '123-12-324' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'AA002' AS SBJT_NO ,'ㅂㅂㅂ' AS ORGN_NM, '124-12-624' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'AA003' AS SBJT_NO ,'ㄴㄴㄴ' AS ORGN_NM, '123-12-424' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'AA001' AS SBJT_NO ,'ㅁㅁㅁ' AS ORGN_NM, '123-12-624' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'AA004' AS SBJT_NO ,'ㅊㅊㅊ' AS ORGN_NM, '123-12-724' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'BB003' AS SBJT_NO ,'ㄴㄴㄴ' AS ORGN_NM, '123-64-546' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 1 AS SN, 'CC005' AS SBJT_NO ,'ㅇㅇㅇ' AS ORGN_NM, '123-12-324' AS BSNS_REG_NO FROM DUAL UNION ALL
SELECT 2 AS SN, 'CC001' AS SBJT_NO ,'ㅇㅇㅇ' AS ORGN_NM, '123-12-324' AS BSNS_REG_NO FROM DUAL) A
GROUP BY A.SN,A.ORGN_NM
ORDER BY TO_NUMBER(SN),A.ORGN_NM
;
'오라클' 카테고리의 다른 글
로또 생성 쿼리 (0) | 2017.02.03 |
---|---|
오라클 락 & 킬 lock kill (0) | 2016.08.12 |
오라클 컬림 및 테이블 정보 조회. (0) | 2016.07.14 |
[ORACLE] 패스워드 정책, ORA-28000 : the account is locked (0) | 2015.11.25 |
Oracle 날짜 및 시간 관련 함수 (0) | 2015.11.06 |