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