Re: Selecting rows as columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 16 Feb 1999 19:13:44 GMT
Message-ID: <36cac275.21855426@192.86.155.100>

A copy of this was sent to bkelly01_at_hotmail.com (if that email address didn't require changing) On Tue, 16 Feb 1999 17:35:11 GMT, you 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
>

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. Received on Tue Feb 16 1999 - 13:13:44 CST