Re: SQL Question

Hi Ron:

Yes I have somes ideas, but I did not test them.

1. Example: Create view myview ... select a.invoice_number, b.item1, c.item2, d.item3, e.item4, ... from myheader a , ( select item item1 from myitems where invoice_number = a.invoice_number ) b , ( select item item2 from myitems where invoice_number = a.invoice_number ) c , ( select item item3 from myitems where invoice_number = a.invoice_number ) d , ( select item item4 from myitems where invoice_number = a.invoice_number ) e ...
2. Example: Create view myview ... select a.invoice_number, b.item1, c.item2, d.item3, e.item4, ... from myheader a , ( select item, invoice_number from myitems ) b , ( select item, invoice_number from myitems ) c , ( select item, invoice_number from myitems ) d , ( select item, invoice_number from myitems ) e where a.invoice_number = b.invoice_number (+) and a.invoice_number = c.invoice_number (+) and a.invoice_number = d.invoice_number (+) and a.invoice_number = e.invoice_number (+) ...
3. Or maybe: create view myview ... select invoice_number, item, 0, 0, 0, 0, ... from myitems where item =1 union select invoice_number, 0, item, 0, 0, 0, 0, ... from myitems where item =2 union ...

create view mysecondview
...
select invoice_number, item1, item2...
from myview
group by invoice_number.

I think they are not nice, but anyway I hope it helps.

Mabel,

Ron Many <rmany_at_pacbell.net> wrote in article <34D772AD.FAB819E4_at_pacbell.net>...
> I like to return information from several rows in a single row like
> this:
>
> I have a table with line items information that is the detail for an
> invoice header table. each invoice has between 1 to 10 line items
> referenced by an Invoice Number column. What I need to do is to create a
> view with a single string contains all the item numbers of an invoice
> concatenate.
>
> I don't want to use cursor or PL/SQL since I like to include it in a
> view.
>
> Can I do it? any ideas how?
>
> Thank You,
> Ron
>
>
Received on Tue Feb 03 1998 - 00:00:00 CST