Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using multiple 'for' statements in an 11g pivot query

Guess2Jun 6 2013 — edited Jun 7 2013
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)
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 5 2013
Added on Jun 6 2013
2 comments
789 views