Re: concatenate the same column with mulitple rows

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 1 Dec 2007 07:07:33 -0800 (PST)
Message-ID: <2c6eac7b-8466-4a39-a600-f66def5f7989@s19g2000prg.googlegroups.com>

On Nov 30, 11:33 pm, 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

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.databases.oracle.server/browse_frm/thread/232ba4b0028bd1f5/cdee1d79caf2c73a?lnk=st&q=#cdee1d79caf2c73a

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 -- Received on Sat Dec 01 2007 - 09:07:33 CST