Re: Retrieving 4 records as 1 record

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 26 May 1998 17:21:45 GMT
Message-ID: <356df9df.18315336@192.86.155.100>

A copy of this was sent to Abeeda_Mohammed_at_xn.xerox.com (if that email address didn't require changing) On Tue, 26 May 1998 14:48:19 GMT, you wrote:

>Hello,
>
>I have a table with the following fields :
>
>Order_no,
>Order_followup_no,
>orderstatus
>
>If the records in the table are
>
>Order# Order_followup# Orderstatus
>1 101 PLACED
>1 102 RECIVED
>1 103 ANSWERED
>1 104 COMPLETE
>2 101 PLACED
>2 102 RECEIVED
>2 103 ANSWERED
>2 104 COMPLETE
>
>I want to create a view which displays
>
>1 PLACED RECEIVED ANSWERED COMPLETE
>2 PLACED RECEIVED ANSWERED COMPLETE
>
>How do I do this?

decode will help you do this:

select order#,

       max( decode( orderstatus, 'PLACED',   orderstatus, NULL ) ),
       max( decode( orderstatus, 'RECEIVED', orderstatus, NULL ) ),
       max( decode( orderstatus, 'ANSWERED', orderstatus, NULL ) ),
       max( decode( orderstatus, 'COMPLETE', orderstatus, NULL ) )

  from T
 group by order#

>
>Thank you,
>
>Abeeda
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 May 26 1998 - 12:21:45 CDT