특정 월의 일자를 가로로 나열한 후 조인 0 1 544

by OraTaDo [SQL Query] [2020.03.28 17:15:18]


(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 입니다)

by 마농 [2020.03.30 08:18:45]

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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입