Re: SQL Question

You could do the following:
1. Write this function:

  create or replace function F_ITEMS_CONCAT

         (P_INVOICE_NR	in Number
         )return varchar2 is

    cursor C_ITEMS is
	select ITEM_NR
          from ITEMS_TABLE
	 where INVOICE_NR = P_INVOICE_NR;

    CONCAT_STRING varchar2(2000);

  begin

     for R_ITEM in C_ITEMS loop
	 /* A string of all items seperated with e.g. a ';' is built */
	 CONCAT_STRING = CONCAT_STRING||TO_CHAR(R_ITEM.ITEM_NR)||';'; 
     end loop;
  

    return CONCAT_STRING;

  end F_ITEMS_CONCAT;

2. Build the View using this function:

create or replace view THE_VIEW as
  select INVOICE_NR, F_ITEMS_CONCAT(INVOICE_NR) "ITEMS_CONCAT"     from ITEMS_TABLE
   group by INVOICE_NR;

Angelo.
Ron Many wrote:
>
> 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 Wed Feb 04 1998 - 00:00:00 CET