with t as ( select 1 seq, '1' num from dual union all select 1 seq, '2' num from dual union all select 1 seq, '3' num from dual union all select 1 seq, '4' num from dual union all select 1 seq, '5' num from dual union all select 1 seq, '6' num from dual union all select 1 seq, '7' num from dual union all select 1 seq, '8' num from dual union all select 2 seq, '9' num from dual union all select 2 seq, 'A' num from dual union all select 1 seq, 'B' num from dual union all select 1 seq, 'C' num from dual union all select 1 seq, 'D' num from dual ) select seq,listagg(num,',') within group(order by to_number(regexp_substr(num,'[^~]+',1,1),'XX')) numlist from ( select seq, decode(min(num),max(num),min(num),min(num)||'~'||max(num)) num from ( select seq , num , to_number(num,'XX') numtran , row_number() over(partition by seq order by to_number(num,'XX')) rn from t ) group by seq, numtran - rn ) group by seq
제가 잘 이해한 건지 모르겠네요 seq별로 연속인 것들끼리 묶어서 표현하는 거 맞나요?
WITH A AS ( SELECT 1 SEQ, '1' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '2' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '3' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '4' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '5' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '6' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '8' PNO FROM DUAL UNION ALL SELECT 1 SEQ, '9' PNO FROM DUAL UNION ALL SELECT 1 SEQ, 'A' PNO FROM DUAL UNION ALL SELECT 1 SEQ, 'B' PNO FROM DUAL UNION ALL SELECT 2 SEQ, '1' PNO FROM DUAL UNION ALL SELECT 2 SEQ, '2' PNO FROM DUAL UNION ALL SELECT 2 SEQ, '3' PNO FROM DUAL UNION ALL SELECT 2 SEQ, '7' PNO FROM DUAL UNION ALL SELECT 2 SEQ, '9' PNO FROM DUAL UNION ALL SELECT 2 SEQ, 'C' PNO FROM DUAL UNION ALL SELECT 2 SEQ, 'D' PNO FROM DUAL UNION ALL SELECT 2 SEQ, 'E' PNO FROM DUAL UNION ALL SELECT 2 SEQ, 'F' PNO FROM DUAL ) SELECT SEQ, ListAgg(NO, ',') WITHIN GROUP(ORDER BY SEQ) 포장 FROM ( SELECT SEQ , MIN(PNO)||DECODE(MIN(PNO),MAX(PNO),NULL,'~'||MAX(PNO)) NO FROM ( SELECT SEQ, PNO , TO_NUMBER(PNO,'XX') - ROW_NUMBER () OVER (PARTITION BY SEQ ORDER BY PNO) AS GP FROM A ) GROUP BY SEQ, GP ) GROUP BY SEQ ;
SELECT seq , LISTAGG(x, ',') WITHIN GROUP(ORDER BY s) FROM (SELECT seq , MIN(x) s , TO_CHAR(MIN(x), 'fmXX') || DECODE(COUNT(*), 1, '', '~'||TO_CHAR(MAX(x), 'fmXX')) x FROM (SELECT seq, num , TO_NUMBER(num, 'xx') x , ROW_NUMBER() OVER( PARTITION BY seq ORDER BY TO_NUMBER(num, 'xx')) rn FROM t ) GROUP BY seq, x - rn ) GROUP BY seq ORDER BY seq ;