Home » SQL & PL/SQL » SQL & PL/SQL » month as columns (12c)
month as columns [message #676076] Sun, 12 May 2019 11:25 Go to next message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
I have to write a query based on count of transactions per month and year, basically i want to show months as columns and year / transaction type as rows.


create table vouch_count(vouch_code varchar2(12),vouch_year_mth varchar2(12),vouch_count number);

insert into vouch_count(vouch_code,vouch_year_mth,vouch_count) values ('BKP','201801',40);
insert into vouch_count(vouch_code,vouch_year_mth,vouch_count) values ('BKC','201802',50);
insert into vouch_count(vouch_code,vouch_year_mth,vouch_count) values ('BKP','201901',100);
insert into vouch_count(vouch_code,vouch_year_mth,vouch_count) values ('BKC','201901',90) ;


--I wrote this query

SELECT * FROM (
SELECT VOUCH_CODE,VOUCH_YEAR_MTH,VOUCH_COUNT 
FROM VOUCH_COUNT
)
 PIVOT ( sum(VOUCH_count)
   FOR voucH_year_mTH IN ('201801','201802'))

--output is as follows.

      201801  201802
BKP	40	
BKC		50

-- i want the output to be as

             01     02
BKP   2018   40     50
      2019          
BKC   2018   100    90        
      2019   
      


Re: month as columns [message #676078 is a reply to message #676076] Sun, 12 May 2019 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i want the output to be as
The output does not match the data!

SQL> break on vouch_code
SQL> set numwidth 5
SQL> select * from vouch_count order by vouch_code, vouch_year_mth;
VOUCH_CODE   VOUCH_YEAR_M VOUCH_COUNT
------------ ------------ -----------
BKC          201802                50
             201901                90
BKP          201801                40
             201901               100

4 rows selected.

SQL> with
  2    y as (select distinct to_number(substr(vouch_year_mth,1,4)) y from vouch_count),
  3    minmax as (select min(y) miny, max(y) maxy from y),
  4    years as (
  5      select miny+level-1 yr
  6      from minmax
  7      connect by level <= maxy-miny+1
  8    )
  9  select vouch_code, yr "YEAR",
 10         sum(decode(substr(vouch_year_mth,5,2),'01',vouch_count)) "01",
 11         sum(decode(substr(vouch_year_mth,5,2),'02',vouch_count)) "02",
 12         sum(decode(substr(vouch_year_mth,5,2),'03',vouch_count)) "03",
 13         sum(decode(substr(vouch_year_mth,5,2),'04',vouch_count)) "04",
 14         sum(decode(substr(vouch_year_mth,5,2),'05',vouch_count)) "05",
 15         sum(decode(substr(vouch_year_mth,5,2),'06',vouch_count)) "06",
 16         sum(decode(substr(vouch_year_mth,5,2),'07',vouch_count)) "07",
 17         sum(decode(substr(vouch_year_mth,5,2),'08',vouch_count)) "08",
 18         sum(decode(substr(vouch_year_mth,5,2),'09',vouch_count)) "09",
 19         sum(decode(substr(vouch_year_mth,5,2),'10',vouch_count)) "10",
 20         sum(decode(substr(vouch_year_mth,5,2),'11',vouch_count)) "11",
 21         sum(decode(substr(vouch_year_mth,5,2),'12',vouch_count)) "12"
 22  from years left outer join vouch_count partition by (vouch_code)
 23       on yr = to_number(substr(vouch_year_mth,1,4))
 24  group by vouch_code, yr
 25  order by vouch_code, yr
 26  /
VOUCH_CODE    YEAR    01    02    03    04    05    06    07    08    09    10    11    12
------------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
BKC           2018          50
              2019    90
BKP           2018    40
              2019   100

4 rows selected.
Re: month as columns [message #676079 is a reply to message #676078] Sun, 12 May 2019 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

or with PIVOT:
SQL> with
  2    data as (
  3      select vouch_code,
  4             to_number(substr(vouch_year_mth,1,4)) "YEAR",
  5             substr(vouch_year_mth,5,2) mnth,
  6             vouch_count
  7      from vouch_count
  8    )
  9  select *
 10  from data
 11       pivot (sum(vouch_count) for mnth in ('01','02','03','04','05','06','07','08','09','10','11','12'))
 12  order by 1, 2
 13  /
VOUCH_CODE    YEAR  '01'  '02'  '03'  '04'  '05'  '06'  '07'  '08'  '09'  '10'  '11'  '12'
------------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
BKC           2018          50
              2019    90
BKP           2018    40
              2019   100

4 rows selected.
Re: month as columns [message #676142 is a reply to message #676079] Thu, 16 May 2019 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: month as columns [message #676143 is a reply to message #676142] Thu, 16 May 2019 01:34 Go to previous message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
Thanks Michael, your solution is perfect.
Previous Topic: How to get or concatenate a value from inner most query
Next Topic: write to the file from PL SQL
Goto Forum:
  


Current Time: Fri Nov 22 19:05:01 CST 2019