oracle: 11.2.0.3
Basically I need to pivot around 2 different columns (I think I need to 'for' clauses').
See below.
Below is pseudo code from non-11g pivot statement.
I am trying to figure out how to do with pivot.
-- note field names are pseudo code and not real values
-- note that the field after 'then' in the case statement is different for the 2 pivots
select my_id,
max( (case when myfield = 'MYVALUE1' then mydate_date else null end)) as MYVALUE1,
to_number(max( (case when myfield = 'MYVALUE' then myfieldvalue else null end))) as MYVALUE2,
min (insert_date ) insert_date
from mytable
group by myid
so if I'm doing this in 11g pivot syntax I am stuck at:
-- if I add max(fieldvalue), I think I will pivot too many times. I just want 2 extra columns. Can I add a second 'for' statement?
select *
from mytable
pivot (
max(mydate)
for myfield in ('MYVALUE1' as MYVALUE1)
)