(A) 테이블 select to_char(dt + level - 1, 'yyyymmdd') dt from (select to_date('202004', 'yyyymm') dt from dual) connect by level <= last_day(dt) - dt + 1
(B) 테이블 with rTable as ( select '2020-04-01' rDate, 85 rCnt, '0820/1330' rTime from dual union all select '2020-04-02' rDate, 10 rCnt, '0820/1330' rTime from dual union all select '2020-04-03' rDate, 11 rCnt, '0820/1330' rTime from dual union all select '2020-04-04' rDate, 77 rCnt, '0820/1330' rTime from dual union all select '2020-04-05' rDate, 35 rCnt, '0820/1330' rTime from dual union all select '2020-04-06' rDate, 45 rCnt, '0820/1330' rTime from dual union all select '2020-04-07' rDate, 15 rCnt, '0820/1330' rTime from dual union all select '2020-04-08' rDate, 78 rCnt, '0820/1330' rTime from dual union all select '2020-04-09' rDate, 33 rCnt, '0820/1330' rTime from dual union all select '2020-04-10' rDate, 32 rCnt, '0820/1330' rTime from dual union all select '2020-04-11' rDate, 99 rCnt, '0820/1330' rTime from dual union all select '2020-04-12' rDate, 21 rCnt, '0820/1330' rTime from dual union all select '2020-04-13' rDate, 34 rCnt, '0820/1330' rTime from dual union all select '2020-04-14' rDate, 28 rCnt, '0820/1330' rTime from dual union all select '2020-04-17' rDate, 68 rCnt, '0820/1330' rTime from dual union all select '2020-04-18' rDate, 65 rCnt, '0820/1330' rTime from dual union all select '2020-04-19' rDate, 12 rCnt, '0820/1330' rTime from dual union all select '2020-04-20' rDate, 7 rCnt, '0820/1330' rTime from dual union all select '2020-04-21' rDate, 22 rCnt, '0820/1330' rTime from dual union all select '2020-04-22' rDate, 33 rCnt, '0820/1330' rTime from dual union all select '2020-04-23' rDate, 99 rCnt, '0820/1330' rTime from dual union all select '2020-04-25' rDate, 12 rCnt, '0820/1330' rTime from dual union all select '2020-04-27' rDate, 85 rCnt, '0820/1330' rTime from dual union all select '2020-04-28' rDate, 91 rCnt, '0820/1330' rTime from dual union all select '2020-04-29' rDate, 16 rCnt, '0820/1330' rTime from dual union all select '2020-04-30' rDate, 90 rCnt, '0820/1330' rTime from dual ) select * from rTable
(A) 와 (B) 테이블이 있습니다.
항목 (A)20200401 (A)20200402 (A)20200403 (A)20200404 (A)20200405 (A)20200406 .....생략...... (A)20200430
(B)RCNT컬럼
(B)RTIME컬럼
위 결과 처럼 와 같이 만들 수 있을까요? (Oracle 10g 입니다)
SQL만으로 컬럼을 가변으로 처리할 수는 없습니다.
가변으로 처리하려면 동적쿼리를 사용해야 합니다.
A테이블을 읽어 프로그램 루프 돌려가면서 B 를 읽어내는 쿼리문장 완성하여 다시 실행.
다만, 이 경우에는 1개월치 자료 조회이므로 31개 일자 컬럼 고정으로 간다고 보고 작성하면 됩니다.
WITH rTable AS ( SELECT '2020-04-01' rDate, 85 rCnt, '0820/1330' rTime FROM dual UNION ALL SELECT '2020-04-02', 10, '0820/1330' FROM dual UNION ALL SELECT '2020-04-03', 11, '0820/1330' FROM dual UNION ALL SELECT '2020-04-04', 77, '0820/1330' FROM dual UNION ALL SELECT '2020-04-05', 35, '0820/1330' FROM dual UNION ALL SELECT '2020-04-06', 45, '0820/1330' FROM dual UNION ALL SELECT '2020-04-07', 15, '0820/1330' FROM dual UNION ALL SELECT '2020-04-08', 78, '0820/1330' FROM dual UNION ALL SELECT '2020-04-09', 33, '0820/1330' FROM dual UNION ALL SELECT '2020-04-10', 32, '0820/1330' FROM dual UNION ALL SELECT '2020-04-11', 99, '0820/1330' FROM dual UNION ALL SELECT '2020-04-12', 21, '0820/1330' FROM dual UNION ALL SELECT '2020-04-13', 34, '0820/1330' FROM dual UNION ALL SELECT '2020-04-14', 28, '0820/1330' FROM dual UNION ALL SELECT '2020-04-17', 68, '0820/1330' FROM dual UNION ALL SELECT '2020-04-18', 65, '0820/1330' FROM dual UNION ALL SELECT '2020-04-19', 12, '0820/1330' FROM dual UNION ALL SELECT '2020-04-20', 7, '0820/1330' FROM dual UNION ALL SELECT '2020-04-21', 22, '0820/1330' FROM dual UNION ALL SELECT '2020-04-22', 33, '0820/1330' FROM dual UNION ALL SELECT '2020-04-23', 99, '0820/1330' FROM dual UNION ALL SELECT '2020-04-25', 12, '0820/1330' FROM dual UNION ALL SELECT '2020-04-27', 85, '0820/1330' FROM dual UNION ALL SELECT '2020-04-28', 91, '0820/1330' FROM dual UNION ALL SELECT '2020-04-29', 16, '0820/1330' FROM dual UNION ALL SELECT '2020-04-30', 90, '0820/1330' FROM dual ) SELECT lv , MIN(DECODE(d, '01', v)) d01 , MIN(DECODE(d, '02', v)) d02 , MIN(DECODE(d, '03', v)) d03 , MIN(DECODE(d, '04', v)) d04 , MIN(DECODE(d, '05', v)) d05 , MIN(DECODE(d, '06', v)) d06 , MIN(DECODE(d, '07', v)) d07 , MIN(DECODE(d, '08', v)) d08 , MIN(DECODE(d, '09', v)) d09 , MIN(DECODE(d, '10', v)) d10 , MIN(DECODE(d, '11', v)) d11 , MIN(DECODE(d, '12', v)) d12 , MIN(DECODE(d, '13', v)) d13 , MIN(DECODE(d, '14', v)) d14 , MIN(DECODE(d, '15', v)) d15 , MIN(DECODE(d, '16', v)) d16 , MIN(DECODE(d, '17', v)) d17 , MIN(DECODE(d, '18', v)) d18 , MIN(DECODE(d, '19', v)) d19 , MIN(DECODE(d, '20', v)) d20 , MIN(DECODE(d, '21', v)) d21 , MIN(DECODE(d, '22', v)) d22 , MIN(DECODE(d, '23', v)) d23 , MIN(DECODE(d, '24', v)) d24 , MIN(DECODE(d, '25', v)) d25 , MIN(DECODE(d, '26', v)) d26 , MIN(DECODE(d, '27', v)) d27 , MIN(DECODE(d, '28', v)) d28 , MIN(DECODE(d, '29', v)) d29 , MIN(DECODE(d, '30', v)) d30 , MIN(DECODE(d, '31', v)) d31 FROM (SELECT lv , SUBSTR(rdate, 9, 2) d , DECODE(lv, 1, TO_CHAR(rcnt), rtime) v FROM rTable , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) WHERE rdate LIKE '2020-04%' ) GROUP BY lv ;