Re: Combining Rows in a SQL Query

Ian Holsman wrote:
>
> Hi
> I would like to find out if it is possible via a SQL query to do something
> like this:
> given a invoice -- invoice item table structure have a SQL query show me
> something like
>
> Invoice Products
> ---------- ---------------
> 1012 a,b,c,d
> 1013 a,e,f
>
> this input table would be some thing like
>
> Invoice Produc
> t
> ---------- ------------
> 1012 a
> 1012 b
> 1012 c
> 1012 d
> 1013 a
> 1013 e
> 1013 f
>
> does anybody know who to do this without using a cursor in PL/SQL ???
>
> Thank you
>
> Ian Holsman
> IanHolsman_at_Iname.com

If the list of invoice items per invoice is of arbitrary length, I don't think this is possible using standard SQL. However, I have used PL/SQL to define a list function which can be referenced in a standard SQL statement as below. (I used PL/SQL to define the function, but you could do it in Pro* if you really wanted to :o).

create or replace function item_list

   (inv_num IN number)
   return varchar2 as

   cursor C (inv_no IN number) is

      select product from invoice_items
      where  invoice_no = inv_no
      order by product; -- optional, but  desirable

   comma_list varchar2 (1000) := null; -- choose your max length!

begin

   for R in C (inv_num)
   loop

      comma_list := comma_list||R.product||',';    end loop;
   return rtrim(comma_list,','); -- remove final comma end item_list;

You can then use this function in a standard SQL statement in, for example, SQL*Plus:

column prod_list format a72 wrap word;
select invoice_no, item_list(invoice_no) prod_list from invoices [where ...]

Hope this helps.

Chrysalis. Received on Sat May 24 1997 - 00:00:00 CDT