## Forum Stats

• 3,851,487 Users
• 2,263,988 Discussions

Discussions

# Pivot on multiple variables

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,

Tagged:

• 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));`

YRJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
2017000000001124
2016000000001135

• 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.

• `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));`

YEARJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
2017 1124
2016 1135
• 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`
• 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));`

YRJAN_CNT_AAJAN_CNT_ABJAN_CNT_OTHERJAN_CNT_ALLFEB_CNT_AAFEB_CNT_ABFEB_CNT_OTHERFEB_CNT_ALLMAR_CNT_AAMAR_CNT_ABMAR_CNT_OTHERMAR_CNT_ALL
2017000000001124
2016000000001135
• 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

This discussion has been closed.