Monthly Archives: July 2021

pivot을 사용해서 날짜별로 값을 보여주기

시작일자, 종료일자를 가지고 있는 원본데이터와 캘린더 데이터를 조인해서 일별로 사용자의 실적을 만들어 준 후에 pivot을 사용해서 한줄로 사용자의 실적을 보여준다.

SELECT *
FROM (
    SELECT AA.NAME, SUBSTR(BB.DT_DD,-2) AS DT_DD
    FROM  
        (
            select 'HADOOH1' as name , '20210701' as startdt, '20210708' as enddt from dual union all
            select 'HADOOH2' as name , '20210710' as startdt, '20210715' as enddt from dual union all
            select 'HADOOH3' as name , '20210718' as startdt, '20210730' as enddt from dual
        ) AA,
        (
            SELECT *  -- 해당월 캘린더정보
            FROM CALENDAR
            WHERE DT_DD LIKE '202107%'
        ) BB
    WHERE 1=1
        AND BB.DT_DD BETWEEN AA.STARTDT AND AA.ENDDT
    )
PIVOT (
        COUNT(*)
        FOR DT_DD 
        IN ('01' as D01,'02' as D02,'03' as D03,'04' as D04,'05' as D05,'06' as D06,'07' as D07,'08' as D08,'09' as D09,'10' as D10
        ,'11' as D11,'12' as D12,'13' as D13,'14' as D14,'15' as D15,'16' as D16,'17' as D17,'18' as D18,'19' as D19,'20' as D20
        ,'21' as D21,'22' as D22,'23' as D23,'24' as D24,'25' as D25,'26' as D26,'27' as D27,'28' as D28,'29' as D29,'30' as D30
        ,'31' as D31)
      )

원본데이터
결과값