데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

WITH t(day, val) AS(

SELECT '월요일', 1 FROM dual UNION ALL

SELECT '월요일', 2 FROM dual UNION ALL

SELECT '월요일', 3 FROM dual UNION ALL

SELECT '월요일', 7 FROM dual UNION ALL

SELECT '월요일', 8 FROM dual UNION ALL

SELECT '수요일', 2 FROM dual UNION ALL

SELECT '수요일', 3 FROM dual UNION ALL

SELECT '수요일', 9 FROM dual

)

SELECT LISTAGG(day||'('||val||')', ' ') WITHIN

GROUP(ORDER BY rn) val

FROM (SELECT rn, day, LISTAGG(val, ',') WITHIN

GROUP(ORDER BY rn, val) val

FROM (SELECT rn, day

, DECODE(MIN(val), MAX(val)

, TO_CHAR(MIN(val)), MIN(val)||'~'||MAX(val)) val

FROM (SELECT day, val

, DECODE(day, '월요일', 1, '화요일', 2

, '수요일', 3, '목요일', 4

, '금요일', 5, '토요일', 6

, '일요일', 7) rn

FROM t

ORDER BY rn, val

)

GROUP BY rn, day, val - ROWNUM

)

GROUP BY rn, day

)

아린(arin76)님이 2013-07-31 18:24에 작성한 댓글입니다.
이 댓글은 2013-07-31 18:25에 마지막으로 수정되었습니다.

WITH t AS
(
SELECT '월요일' dy, 1 sq FROM dual
UNION ALL SELECT '월요일', 2 FROM dual
UNION ALL SELECT '월요일', 3 FROM dual
UNION ALL SELECT '월요일', 7 FROM dual
UNION ALL SELECT '월요일', 8 FROM dual
UNION ALL SELECT '수요일', 2 FROM dual
UNION ALL SELECT '수요일', 3 FROM dual
UNION ALL SELECT '수요일', 9 FROM dual
)
SELECT SUBSTR(
       XMLAGG(XMLELEMENT(z,' ',dy,'(',y,')')ORDER BY d).EXTRACT('//text()')
       , 2) z
FROM (SELECT dy
             , INSTR('월화수목금토일', SUBSTR(dy, 1, 1)) d
             , SUBSTR(
               XMLAGG(XMLELEMENT(y,', ',x)ORDER BY grp).EXTRACT('//text()')
               , 3) y
          FROM (SELECT dy, grp
                     , MIN(sq)||DECODE(COUNT(*), 1, '', '~'||MAX(sq)) x
                  FROM (SELECT dy, sq
                             , sq
                             - ROW_NUMBER() OVER(PARTITION BY dy
                                                     ORDER BY sq) grp
                          FROM t
                        )
                 GROUP BY dy, grp
                )
         GROUP BY dy
        )
;

마농(manon94)님이 2013-07-31 18:33에 작성한 댓글입니다.
이 댓글은 2013-07-31 18:35에 마지막으로 수정되었습니다.