Pivot on multiple variables

user13117585

Hello again,

I'm sorry to bother you again with my stupid questions, but I was hoping someone can advise on a better approach.

Imagine I have the following table:

CREATE TABLE foo (  fid NUMBER(10),   ftype VARCHAR2(100),  created_on DATE);INSERT INTO foo(fid, ftype, created_on) VALUES(1, 'AA', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(2, 'AB', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(3, 'XX', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(4, 'XY', SYSDATE);INSERT INTO foo(fid, ftype, created_on) VALUES(5, 'AA', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(6, 'AB', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(7, 'XX', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(8, 'XY', SYSDATE - INTERVAL '1' YEAR);INSERT INTO foo(fid, ftype, created_on) VALUES(9, 'XZ', SYSDATE - INTERVAL '1' YEAR);

I would like to have for each year, a column that has the total amont of each type like the following query does... But the thing is that it is very tedious to write for all the months.

SELECT EXTRACT(YEAR FROM created_on) AS year,        /* jan */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_aa_jan,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_ab_jan,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_other_jan,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 1 THEN 1 END) AS cnt_all_jan,       /* feb */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_aa_feb,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_ab_feb,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_other_feb,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 2 THEN 1 END) AS cnt_all_feb,       /* mar */       COUNT(CASE WHEN ftype = 'AA' AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_aa_mar,        COUNT(CASE WHEN ftype = 'AB' AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_ab_mar,        COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') AND EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_other_mar,       COUNT(CASE WHEN EXTRACT(MONTH FROM created_on) = 3 THEN 1 END) AS cnt_all_mar  FROM foo GROUP BY EXTRACT(YEAR FROM created_on);

Do you think that it is possible to make this query simpler?

Thank you,

Stew Ashton

You may want 0 instead of NULL in the empty columns, in which case:

with all_months as (  select * from (    select distinct EXTRACT(YEAR FROM created_on) AS yr    from foo  ),(    select level mon,    0 cnt_aa,    0 cnt_ab,    0 cnt_other,    0 cnt_all    from dual connect by level <= 12  )), foo_months as (  select EXTRACT(YEAR FROM created_on) AS yr,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)select * from (  select * from foo_months  union all  select * from all_months)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

YR	JAN_CNT_AA	JAN_CNT_AB	JAN_CNT_OTHER	JAN_CNT_ALL	FEB_CNT_AA	FEB_CNT_AB	FEB_CNT_OTHER	FEB_CNT_ALL	MAR_CNT_AA	MAR_CNT_AB	MAR_CNT_OTHER	MAR_CNT_ALL
2017	0	0	0	0	0	0	0	0	1	1	2	4
2016	0	0	0	0	0	0	0	0	1	1	3	5

BluShadow

What database version are you on?

From 11g, you could use the PIVOT keyword, or there may be some way of using MATCH_RECOGNIZE in 12c (but I don't have 12c database to hand at the moment), but still you're going to need to specify all the columns in one way or another.

Stew Ashton

select * from (  select EXTRACT(YEAR FROM created_on) AS year,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

YEAR	JAN_CNT_AA	JAN_CNT_AB	JAN_CNT_OTHER	JAN_CNT_ALL	FEB_CNT_AA	FEB_CNT_AB	FEB_CNT_OTHER	FEB_CNT_ALL	MAR_CNT_AA	MAR_CNT_AB	MAR_CNT_OTHER	MAR_CNT_ALL
2017									1	1	2	4
2016									1	1	3	5

BluShadow

Or the old fashioned way, factor out the counts to a subquery, and then pivot using the case statements, still lengthy, but not as messy...

(untested so excuse any typos)

WITH c (yr, mth, cnt_aa, cnt_ab, cnt_other, cnt_all) as (        SELECT EXTRACT(YEAR FROM created_on)              ,EXTRACT(MONTH FROM created_on)              ,COUNT(CASE WHEN ftype = 'AA' THEN 1 END)              ,COUNT(CASE WHEN ftype = 'AB' THEN 1 END)              ,COUNT(CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END)              ,COUNT(*)        FROM  foo         GROUP BY EXTRACT(YEAR FROM created_on)                ,EXTRACT(MONTH FROM created_on)      )select year      ,max(case when mth=1 then cnt_aa) as cnt_aa_jan      ,max(case when mth=1 then cnt_ab) as cnt_ab_jan      ,max(case when mth=1 then cnt_other) as cnt_other_jan      ,max(case when mth=1 then cnt_ab) as cnt_all_jan      ,max(case when mth=2 then cnt_aa) as cnt_aa_feb      ,max(case when mth=2 then cnt_ab) as cnt_ab_feb      ,max(case when mth=2 then cnt_other) as cnt_other_feb      ,max(case when mth=2 then cnt_ab) as cnt_all_feb      ,max(case when mth=3 then cnt_aa) as cnt_aa_mar      ,max(case when mth=3 then cnt_ab) as cnt_ab_mar      ,max(case when mth=3 then cnt_other) as cnt_other_mar      ,max(case when mth=3 then cnt_ab) as cnt_all_mar      ,max(case when mth=4 then cnt_aa) as cnt_aa_apr      ,max(case when mth=4 then cnt_ab) as cnt_ab_apr      ,max(case when mth=4 then cnt_other) as cnt_other_apr      ,max(case when mth=4 then cnt_ab) as cnt_all_apr      ,max(case when mth=5 then cnt_aa) as cnt_aa_may      ,max(case when mth=5 then cnt_ab) as cnt_ab_may      ,max(case when mth=5 then cnt_other) as cnt_other_may      ,max(case when mth=5 then cnt_ab) as cnt_all_may      ,max(case when mth=6 then cnt_aa) as cnt_aa_jun      ,max(case when mth=6 then cnt_ab) as cnt_ab_jun      ,max(case when mth=6 then cnt_other) as cnt_other_jun      ,max(case when mth=6 then cnt_ab) as cnt_all_jun      ,max(case when mth=7 then cnt_aa) as cnt_aa_jul      ,max(case when mth=7 then cnt_ab) as cnt_ab_jul      ,max(case when mth=7 then cnt_other) as cnt_other_jul      ,max(case when mth=7 then cnt_ab) as cnt_all_jul      ,max(case when mth=8 then cnt_aa) as cnt_aa_aug      ,max(case when mth=8 then cnt_ab) as cnt_ab_aug      ,max(case when mth=8 then cnt_other) as cnt_other_aug      ,max(case when mth=8 then cnt_ab) as cnt_all_aug      ,max(case when mth=9 then cnt_aa) as cnt_aa_sep      ,max(case when mth=9 then cnt_ab) as cnt_ab_sep      ,max(case when mth=9 then cnt_other) as cnt_other_sep      ,max(case when mth=9 then cnt_ab) as cnt_all_sep      ,max(case when mth=10 then cnt_aa) as cnt_aa_oct      ,max(case when mth=10 then cnt_ab) as cnt_ab_oct      ,max(case when mth=10 then cnt_other) as cnt_other_oct      ,max(case when mth=10 then cnt_ab) as cnt_all_oct      ,max(case when mth=11 then cnt_aa) as cnt_aa_nov      ,max(case when mth=11 then cnt_ab) as cnt_ab_nov      ,max(case when mth=11 then cnt_other) as cnt_other_nov      ,max(case when mth=11 then cnt_ab) as cnt_all_nov      ,max(case when mth=12 then cnt_aa) as cnt_aa_dec      ,max(case when mth=12 then cnt_ab) as cnt_ab_dec      ,max(case when mth=12 then cnt_other) as cnt_other_dec      ,max(case when mth=12 then cnt_ab) as cnt_all_decfrom  cgroup by yr

Stew Ashton

You may want 0 instead of NULL in the empty columns, in which case:

with all_months as (  select * from (    select distinct EXTRACT(YEAR FROM created_on) AS yr    from foo  ),(    select level mon,    0 cnt_aa,    0 cnt_ab,    0 cnt_other,    0 cnt_all    from dual connect by level <= 12  )), foo_months as (  select EXTRACT(YEAR FROM created_on) AS yr,  extract(month from created_on) AS mon,  case when ftype = 'AA' then 1 end cnt_aa,  case when ftype = 'AB' then 1 end cnt_ab,  CASE WHEN ftype NOT IN ('AA', 'AB') THEN 1 END cnt_other,  1 cnt_all  from foo)select * from (  select * from foo_months  union all  select * from all_months)pivot(   sum(cnt_aa)cnt_aa, sum(cnt_ab)cnt_ab, sum(cnt_other)cnt_other, sum(cnt_all)cnt_all  for mon in(1 jan,2 feb,3 mar));

YR	JAN_CNT_AA	JAN_CNT_AB	JAN_CNT_OTHER	JAN_CNT_ALL	FEB_CNT_AA	FEB_CNT_AB	FEB_CNT_OTHER	FEB_CNT_ALL	MAR_CNT_AA	MAR_CNT_AB	MAR_CNT_OTHER	MAR_CNT_ALL
2017	0	0	0	0	0	0	0	0	1	1	2	4
2016	0	0	0	0	0	0	0	0	1	1	3	5

user13117585

Thank you all of you. I see that my query was not the best and you gave me good ideas to improve. The solution BluShadow suggested is easy to put 0. Thank you