Обсуждение: Joining several rows into only one

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

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
>

> -----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.