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

On Nov 30, 11:33 pm, sheridany <sherid... (AT) yahoo (DOT) com> wrote:
What you want is called a pivot table. There are several possible
approaches. Here is one from the archives:

http://groups.google.com/group/comp....ee1d79caf2c73a

If the link does not work go to http://groups.google.com and use
advanced search on comp.databases.oracle.* for "pivot table". You
will find many threads. Billy Verreynne has posted a couple of
solutions.

You can also use user written aggregiate functions (see http://asktom.oracle.com)
and I have used the analytic function row_number to help pivot rows
into columns which I just concatenated together.

HTH -- Mark D Powell --

On Dec 1, 4:33 am, sheridany <sherid... (AT) yahoo (DOT) com> wrote:
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.

William Robertson wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org