Re: Retrieving 4 records as 1 record

From: Chris Hamilton <toneczar_at_erols.com>
Date: Tue, 26 May 1998 16:25:04 GMT
Message-ID: <6keqdr$c53$2@goo.nwd.usace.army.mil>

Abeeda_Mohammed_at_xn.xerox.com wrote:

>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?

Meet my good friend Max DeCode ...

create view order_summary
(order#,
 placed,
 received,
 answered,
 complete)
as select
  order#,

  max(decode(orderstatus, 'PLACED', order_followup#, null)),
  max(decode(orderstatus, 'RECEIVED', order_followup#, null)),
  max(decode(orderstatus, 'ANSWERED', order_followup#, null)),
  max(decode(orderstatus, 'COMPLETE', order_followup#, null))

from orders
group by order# ;

Output will be:

Order# Placed Received Answered Complete

    1       101         102          103          104
    2       101         102          103          104

It works with dates, numbers, varchar, whatever.

Chris

---

Chris Hamilton -- ToneCzar_at_erols.com
City of Washington Pipe Band
http://www.serve.com/cowpb/chamilton.html Received on Tue May 26 1998 - 11:25:04 CDT