Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: concatenate the same column with mulitple rows

Re: concatenate the same column with mulitple rows

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 02 Dec 2007 13:51:01 -0800
Message-ID: <1196632261.473995@bubbleator.drizzle.com>


William Robertson wrote:

> On Dec 1, 4:33 am, sheridany <sherid..._at_yahoo.com> wrote:

>> I have an oracle database that has text values (comments) in
>> subsequent rows and I need to add them all together by the primary
>> key.
>>
>> primary key text_field
>> 1111 I like Oracle
>> 1111 It is fast
>> 1111 Their support is great!
>> 2222 Oracle is hard
>>
>> The data would look like this in the result set. We cannot create
>> tables on this db so we only have read access.
>>
>> 1111 I like Oracle It is fast Their support is great!
>> 2222 Oracle is hard
> 
> In 10g you can use the (apparently undocumented) aggregate function
> wm_concat (part of the wmsys schema used for Workspace Management, but
> there is a public synonym). I think it's Tom Kyte's "stragg"
> internally. It uses a comma as the separator though.

WM_CONCAT exists as both a function and a synonym.

In combination with TRANSLATE the commas disappear as demonstrated here: http://www.psoug.org/reference/undocumented.html#uwmc

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Dec 02 2007 - 15:51:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US