Re: Selecting rows as columns

use a select using 4 subqueries in the from clause

select reg.prod_id, reg.Milestone_Date, sumb.Milestone_Date, appr.Milestone_Date,

withd.Milestone_Date
from
(select Prod_ID, Milestone_Date from Product_Milestones where Milestone_ID = 1000) reg,
(select Prod_ID, Milestone_Date from Product_Milestones where Milestone_ID = 1001) subm,
(select Prod_ID, Milestone_Date from Product_Milestones where Milestone_ID = 1002) appr,
(select Prod_ID, Milestone_Date from Product_Milestones where Milestone_ID = 1003) withd
where reg.prod_id = subm.prod_id and reg.prod_id = appr.prod_id and reg.prod_id = withd.prod_id

obviously this would get very messy if your milestone table got any bigger.

bkelly01_at_hotmail.com wrote:

> 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
>
> Any ideas???
>
> Thanks!
>
> Brandon Kelly
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Feb 18 1999 - 21:47:29 CST