Solved

Oracle Pivot Queries

Posted on 2004-10-15
4
1,208 Views
Last Modified: 2007-12-19
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
Comment
Question by:runcsmeduncs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 100 total points
ID: 12319437
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question