데이터베이스 질문과 답변
WITH tmp AS
(
SELECT '20180101' vafrom, '20180105' vato FROM dual
UNION ALL SELECT '20180108', '20180110' FROM dual
UNION ALL SELECT '20180501', '20180516' FROM dual
UNION ALL SELECT '20180201', '20180208' FROM dual
UNION ALL SELECT '20180305', '20180308' FROM dual
UNION ALL SELECT '20180310', '20180312' FROM dual
)
SELECT a.ym
, b.rn
, c.vafrom||'-'||c.vato VACA
FROM (SELECT TO_CHAR(ADD_MONTHS(sym, LEVEL - 1), 'yyyymm') ym
FROM (SELECT TO_DATE('201801', 'yyyymm') sym -- 시작월
, TO_DATE('201805', 'yyyymm') eym -- 종료월
FROM dual)
CONNECT BY LEVEL <= MONTHS_BETWEEN(eym, sym) + 1
) a
CROSS JOIN
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 5) b
LEFT OUTER JOIN
(SELECT SUBSTR(vafrom, 1, 6) ym
, ROW_NUMBER() OVER(PARTITION BY SUBSTR(vafrom, 1, 6) ORDER BY vafrom, vato) rn
, vafrom, vato
FROM tmp
WHERE vafrom >= '201801' || '01' -- 시작월
AND vafrom <= '201805' || '31' -- 종료월
) c
ON a.ym = c.ym
AND b.rn = c.rn
ORDER BY a.ym, b.rn
마농님이 일전에 도움을 주신sql입니다
이것을 행으로 나열하고 싶습니다.
| 0101 | 0102 | 0103 | 0104 | 0105 | 0201 | 0202 | |||
| 20180101-20180105 | 20180108-20180110 | - | - | - | 20180201-20180208 | - |
null인경우 '-'있어도 되고 없어도 됩니다.
몇줄안되면 노가다라도 해서 써보겠는데 1~5월 까지 1월 5개 2월 5개 ...총 25열이 나와야해서... 고수의 도움을 받고자 합니다.
WITH tmp AS
(
SELECT '20180101' vafrom, '20180105' vato FROM dual
UNION ALL SELECT '20180108', '20180110' FROM dual
UNION ALL SELECT '20180501', '20180516' FROM dual
UNION ALL SELECT '20180201', '20180208' FROM dual
UNION ALL SELECT '20180305', '20180308' FROM dual
UNION ALL SELECT '20180310', '20180312' FROM dual
)
SELECT *
FROM (SELECT SUBSTR(vafrom, 1, 6) ym
, ROW_NUMBER() OVER(PARTITION BY SUBSTR(vafrom, 1, 6) ORDER BY vafrom, vato) rn
, vafrom ||'-'|| vato v
FROM tmp
WHERE vafrom >= '201801' || '01' -- 시작월
AND vafrom <= '201805' || '31' -- 종료월
)
PIVOT (MIN(v) FOR rn IN (1, 2, 3, 4, 5))
PIVOT (MIN("1") "1", MIN("2") "2", MIN("3") "3", MIN("4") "4", MIN("5") "5"
FOR ym IN (201801, 201802, 201803, 201804, 201805))
;
마농님 정말 감사합니다. ^____________^
) 버튼을 클릭하여 작성 하시면 됩니다.