Pivot Table

We have a table as below:

SQL> select * from tablea;

ID1 ID2 ID3
----- ----- -----
1 B MAR
1 A JAN
1 A FEB
1 B JAN
1 B JAN
1 B FEB
2 A JAN
2 A JAN
2 A FEB
2 B FEB
2 B FEB
2 B JAN

12 rows selected


Now, we want to for each ID1 and ID2, how many ID3 are 'JAN' & how many ID3 are 'FEB' and how many other than JAN or FEB, in a single row as below:

ID1| ID2| JAN| FEB|OTHER
---| ---| ---| ---|-----
1 | A | 1 | 1 | 0
1 | B | 2 | 1 | 1
2 | A | 2 | 1 | 0
2 | B | 1 | 2 | 0


We can easily do this using decode:

select id1,
id2,
count(decode(id3, 'JAN', 'JAN', null)) JAN,
count(decode(id3, 'FEB', 'FEB', null)) FEB,
count(decode(id3, 'JAN',null,'FEB', null,'OTHER')) OTHER
from tablea
group by id1, id2;

We can easily also do this using case:

select id1,
id2,
count((case when id3='JAN' then 'JAN' else null end)) JAN,
count((case when id3='FEB' then 'FEB' else null end)) FEB,
count((case when id3 not in ('JAN','FEB') then 'OTHER' else null end)) OTHER
from tablea
group by id1, id2;