원하는 출력 데이타도 올려주세요.

WITH t AS
(
SELECT 'FAY0001' v_data FROM dual
UNION ALL SELECT 'FAY0001' FROM dual
UNION ALL SELECT 'FAY0002' FROM dual
UNION ALL SELECT 'FAY0003' FROM dual
UNION ALL SELECT 'FAY0004' FROM dual
UNION ALL SELECT 'FAY0005' FROM dual
UNION ALL SELECT 'FAY0054' FROM dual
UNION ALL SELECT 'FAY0054' FROM dual
UNION ALL SELECT 'FAY0080' FROM dual
UNION ALL SELECT 'FAY0081' FROM dual
UNION ALL SELECT 'FAY0082' FROM dual
)
SELECT LISTAGG(v_data_fr|| DECODE(cnt, 1, '', '~'||v_data_to), ',')
       WITHIN GROUP(ORDER BY v_data_fr) v_data
  FROM (SELECT gb
             , MIN(v_data) v_data_fr
             , MAX(v_data) v_data_to
             , COUNT(DISTINCT v_data) cnt
          FROM (SELECT SUBSTR(v_data, 1, 3) gb
                     , v_data
                     , SUBSTR(v_data, 4, 4)
                     - DENSE_RANK() OVER(
                       PARTITION BY SUBSTR(v_data, 1, 3)
                       ORDER BY SUBSTR(v_data, 4, 4)
                       ) grp
                  FROM t
                )
         GROUP BY gb, grp
        )
 GROUP BY gb
 ORDER BY gb
;