Re: Combining Rows in a SQL Query

From: Will Kooiman <wkooiman_at_csac.com>
Date: 1997/05/28
Message-ID: <338D08B8.B1@csac.com>#1/1

It's possible, but ugly and can only go a few levels deep in the hierarchy, but...

Try something like:

select a.invoice, rtrim(b1.product || ',' ||
                        b2.product || ',' ||
                        b3.product || ',' ||
                        b4.product || ',',
                        ','
                       )
from invoices   a,
     (select rownum, invoice, product from invoices) b1,
     (select rownum, invoice, product from invoices) b2,
     (select rownum, invoice, product from invoices) b3,
     (select rownum, invoice, product from invoices) b4
where a.invoice = b1.invoice  (+)
 and  1         = b1.rownum   (+)
 and  a.invoice = b2.invoice  (+)
 and  2         = b2.rownum   (+)
 and  a.invoice = b3.invoice  (+)
 and  3         = b3.rownum   (+)
 and  a.invoice = b4.invoice  (+)
 and  4         = b4.rownum   (+) 

order by a.invoice;

I wouldn't make a habit of doing code like this, but you asked if it was possible.

Will.

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
 

-- 
======================================================================
Will Kooiman                        Computer Systems Authority
Systems Consultant                  6380 LBJ Freeway, Suite 181
(972) 960-0180 x236                 Dallas, TX 75240
mailto:wkooiman@csac.com            http://www.csac.com

Received on Wed May 28 1997 - 00:00:00 CDT