Re: Selecting rows as columns

Thomas,

Thanks for your help - your suggestion worked perfectly!!

Brandon

>
> >Any help with the following example would be GREATLY appreciated. I am trying
> >to use the rows in my reference table to display as columns in a view, with
> >data from another table.
> >
> >I have a the following two tables: (sample)
> >
> >Table: Milestones [Reference table]
> >
> >Milestone_ID Name Sequence
> >------------ -------------- ------------
> >1000 Registration 1
> >1001 Submission 2
> >1002 Approval 3
> >1003 Withdrawn 4
> >
> >
> >Table: Product_Milestones [Data table]
> >
> >Prod_ID Milestone_ID Milestone_Date
> >-------- ------------ ---------------
> >9000 1000 1/1/1998
> >9000 1001 5/1/1998
> >9000 1002 8/1/1998
> >9001 1000 2/1/1998
> >9001 1001 3/1/1998
> >9001 1002 5/1/1998
> >
> >What I need to do is present this data in a view that looks like this using
> >data in the two tables above:
> >
> >Prod_ID Registration Submission Approval Withdrawn
> >-------- ------------- ----------- --------- ----------
> >9000 1/1/1998 5/1/1998 8/1/1998 Null
> >9001 2/1/1998 3/1/1998 5/1/1998 Null
> >
> >
> >Using the DECODE function, I can get something that looks like this:
> >
> >Prod_ID Registration Submission Approval Withdrawn
> >-------- ------------- ----------- --------- ----------
> >9000 1/1/1998
> >9000 5/1/1998
> >9000 8/1/1998
> >9000 Null
> >9001 2/1/1998
> >9001 3/1/1998
> >9001 5/1/1998
> >9001 Null
> >
>
> so, your current sql statement must look something like:
>
> select prod_id,
> decode( a.name, 'Registration', b.milestone_date, null )
"Registration",
> decode( a.name, 'Submission', b.milestone_date, null ) "Submission",
> decode( a.name, 'Approval', b.milestone_date, null ) "Approval",
> decode( a.name, 'WithDrawn', b.milestone_date, null ) "Withdrawn"
> from milestones a, product_milestones b
> where a.milestone_id = b.mileston_id
> /
>
> if you add the aggregate:
>
> select prod_id,
> max(decode( a.name, 'Registration', b.milestone_date, null ))
"Registration",
> max(decode( a.name, 'Submission', b.milestone_date, null )) "Submission",
> max(decode( a.name, 'Approval', b.milestone_date, null )) "Approval",
> max(decode( a.name, 'WithDrawn', b.milestone_date, null )) "Withdrawn"
> from milestones a, product_milestones b
> where a.milestone_id = b.mileston_id
> group by prod_id
> /
>
> it will collapse out those NULL cells and give you what you want...
>
> >Any ideas???
> >
> >Thanks!
> >
> >
> >Brandon Kelly
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 17 1999 - 21:36:10 CST