• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1211
  • Last Modified:

Oracle Pivot Queries

Hey there experts.

Say for example I have a query that returns a result set like:

Date          Job           Pay          Bonus        Tax
------------------------------------------------------------
07/2004    Manager    80000       5000        32000
07/2004    Clerk         20000        200          1000
08/2004    Clerk         25000        850          1300
08/2004    Cleaner     12000         0             500


What I would like to achieve is to perform a pivot on this result set so that the query returns the figures in the following way:


Job            Pay (July)       Bonus (July)       Tax (July)      Pay (August)       Bonus (August)       Tax (August)    
---------------------------------------------------------------------------------------------------------------------------------
Manager     80000              5000                    32000             0                         0                          0
Clerk          20000              200                       1000           25000                  850                      1300
Cleaner          0                    0                           0              12000                   0                          500


I hope that someone can help me out with this one!!

If you need some scripts to vreate and populate a test table then let me know!!!

Thanks
0
runcsmeduncs
Asked:
runcsmeduncs
1 Solution
 
andrewstCommented:
It will look something like this:

select job
,      sum(DECODE(to_char(date,'Month'),'July', pay)) as "sum_July_pay"
,      sum(DECODE(to_char(date,'Month'),'July', bonus)) as "sum_July_bonus"
,      sum(DECODE(to_char(date,'Month'),'July', tax)) as "sum_July_tax"
,      sum(DECODE(to_char(date,'Month'),'August', pay)) as "sum_August_pay"
,      sum(DECODE(to_char(date,'Month'),'August', bonus)) as "sum_August_bonus"
,      sum(DECODE(to_char(date,'Month'),'August', tax)) as "sum_August_tax"
from   mytable
group by job
order by job
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now