Home » SQL & PL/SQL » SQL & PL/SQL » month as columns (12c)
month as columns [message #676076] |
Sun, 12 May 2019 11:25 |
|
m.abdulhaq
Messages: 254 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 |
|
Michel Cadot
Messages: 68693 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 |
|
Michel Cadot
Messages: 68693 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.
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 27 11:12:04 CDT 2024
|