Q53232055, Oracle - rextester

Language: Layout:

drop table cnr_ticket\\
create table cnr_ticket( ticket_no int, date_created date, subcategory varchar2(50) )\\
insert into cnr_ticket values(1,date'2018-01-01','SENIOR')\\
insert into cnr_ticket values(2,date'2018-02-01','COAN')\\
insert into cnr_ticket values(3,date'2018-02-03','COAN')\\
insert into cnr_ticket values(4,date'2018-11-13','SENIOR')\\
      select *
        from
        ( 
        select subcategory, to_char(date_created,'mm') as month
          from cnr_ticket 
         where to_char(date_created,'yyyy')='2018'
          ) 
         pivot(
               count(*)
               for (month)
                in ('01' as jan ,'02' as feb, '03' as mar,
                    '04' as apr ,'05' as may, '06' as jun,
                    '07' as jul ,'08' as aug, '09' as sep,
                    '10' as oct ,'11' as nov, '12' as dec
                   )
           )\\
           
        select subcategory,
               sum(case when to_char(date_created,'mm') = '01' then 1 else 0 end) as jan,
               sum(case when to_char(date_created,'mm') = '02' then 1 else 0 end) as feb,
               sum(case when to_char(date_created,'mm') = '03' then 1 else 0 end) as mar,
               sum(case when to_char(date_created,'mm') = '04' then 1 else 0 end) as apr,       
               sum(case when to_char(date_created,'mm') = '05' then 1 else 0 end) as may,
               sum(case when to_char(date_created,'mm') = '06' then 1 else 0 end) as jun,
               sum(case when to_char(date_created,'mm') = '07' then 1 else 0 end) as jul,
               sum(case when to_char(date_created,'mm') = '08' then 1 else 0 end) as aug,       
               sum(case when to_char(date_created,'mm') = '09' then 1 else 0 end) as sep,
               sum(case when to_char(date_created,'mm') = '10' then 1 else 0 end) as oct,
               sum(case when to_char(date_created,'mm') = '11' then 1 else 0 end) as nov,
               sum(case when to_char(date_created,'mm') = '12' then 1 else 0 end) as dec              
          from cnr_ticket
         where to_char(date_created,'yyyy')='2018' 
         group by subcategory


absolute service time: 0,9 sec