Postgres Pro
Downloads
Home   >   mailing lists

Thread: Joining several rows into only one

Joining several rows into only one

From
JORGE MALDONADO
Date:
I have a SELECT statement that gives me a result like this:

SONG     ARTIST
-----------------------------------
Song1      Artist1
Song1      Artist2
Song1      Artist3
Song2      Artist2
Song3      Artist4
Song4      Artist1
Song5      Artist4

As you can see, the same song may belong to more than one artist, and this is fine. What I would like to do is NOT to get several rows, one for each different artist, but to join all of the artists in one column and display only one row.
Is this possible?

Respectfully,
Jorge Maldonado

Re: Joining several rows into only one

From
"Jonathan S. Katz"
Date:
On Nov 28, 2012, at 1:33 PM, JORGE MALDONADO wrote:

> I have a SELECT statement that gives me a result like this:
>
> SONG     ARTIST
> -----------------------------------
> Song1      Artist1
> Song1      Artist2
> Song1      Artist3
> Song2      Artist2
> Song3      Artist4
> Song4      Artist1
> Song5      Artist4
>
> As you can see, the same song may belong to more than one artist, and this is fine. What I would like to do is NOT to
getseveral rows, one for each different artist, but to join all of the artists in one column and display only one row. 
> Is this possible?

Hi Jorge,

Try the array_agg function:
SELECT song, array_agg(artist)FROM tableGROUP BY song;

Best,

Jonathan

Re: Joining several rows into only one

From
Oliver d'Azevedo Cristina
Date:
You need to use the array_agg() and array_to_string() functions.

Best,
Oliver

Enviado via iPhone

Em 28/11/2012, às 06:33 PM, JORGE MALDONADO <jorgemal1960@gmail.com> escreveu:

> I have a SELECT statement that gives me a result like this:
>
> SONG     ARTIST
> -----------------------------------
> Song1      Artist1
> Song1      Artist2
> Song1      Artist3
> Song2      Artist2
> Song3      Artist4
> Song4      Artist1
> Song5      Artist4
>
> As you can see, the same song may belong to more than one artist, and this is fine. What I would like to do is NOT to
getseveral rows, one for each different artist, but to join all of the artists in one column and display only one row. 
> Is this possible?
>
> Respectfully,
> Jorge Maldonado
>

Re: Joining several rows into only one

From
"David Johnston"
Date:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Oliver d'Azevedo Cristina
> Sent: Wednesday, November 28, 2012 1:42 PM
> To: JORGE MALDONADO
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Joining several rows into only one
>
> You need to use the array_agg() and array_to_string() functions.
>

Or you can skip directly to the "string_agg(expression, delimiter)" function.

See http://www.postgresql.org/docs/9.2/interactive/functions-aggregate.html for more information on aggregate-related
functions.

David J.