pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoining several rows into only one
4+ messages / 4 participants
[nested] [flat]
* Joining several rows into only one
@ 2012-11-28 18:33 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: JORGE MALDONADO @ 2012-11-28 18:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joining several rows into only one
@ 2012-11-28 18:37 Jonathan S. Katz <jonathan.katz@excoventures.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jonathan S. Katz @ 2012-11-28 18:37 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
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 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?
Hi Jorge,
Try the array_agg function:
SELECT song, array_agg(artist)
FROM table
GROUP BY song;
Best,
Jonathan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joining several rows into only one
@ 2012-11-28 18:42 Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Oliver d'Azevedo Cristina @ 2012-11-28 18:42 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
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 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
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joining several rows into only one
@ 2012-11-28 18:52 David Johnston <polobo@yahoo.com>
parent: Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2012-11-28 18:52 UTC (permalink / raw)
To: 'Oliver d'Azevedo Cristina' <oliveiros.cristina@gmail.com>; 'JORGE MALDONADO' <jorgemal1960@gmail.com>; +Cc: pgsql-sql
> -----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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-11-28 18:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-28 18:33 Joining several rows into only one JORGE MALDONADO <jorgemal1960@gmail.com>
2012-11-28 18:37 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2012-11-28 18:42 ` Oliver d'Azevedo Cristina <oliveiros.cristina@gmail.com>
2012-11-28 18:52 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox