Hello everyone,
(tldr down below)
-- edit: Oracle 11g
I have a problem with pivots ive been trying to figure out for quite a while now so I'm hoping someone here can help me:
Pivots and how I use them:
I want to show for week 01 to week 05 for this year, the profit all the people from sales made for every week seperately in columns:
Code:
select employee, week, profit
from randomtable
where week in ('201101','201102','201103','201104','201105')
This will give me results as follow:
Emp0001 201101 203847
Emp0001 201103 27009
Emp0003 201101 238022
BUT I want it displayed like this:
Employee | Week01 | Week02 | Week03 | Week04 | Week05
data | data | data | data | data | data
To achieve this I just take the previous query and add the pivot statement:
Code:
select * from (
select employee, week, profit
from randomtable
where week in ('201101','201102','201103','201104','201105')
) pivot (max(profit) for week in ('201101','201102','201103','201104','201105'))
MY PROBLEM
Now having pivoted this statement (which was easy) I want to make the weeks dynamic, I want it to show the last 5 weeks (>sysdate-35) and also take this into the pivot. Now I cannot use a subquery or a range in the 'for week in ()' part of the pivot, so how can I solve this?
TLDR:
I want my pivot columns to be dynamic/be created from a subquery/range; how?
dBforums
>
Impossible Pivot?
01-25-12, 05:26
Linear Mode
