PostgreSQL SQL - Concatenate strings using GROUP BY

Author

Concatenate strings using GROUP BY

Philippe Lang

2006-02-09, 7:24 am

Hi,

Let's say we have a query like:

SELECT id, fk, str
FROM foo

.... which returns:

id fk str
---------------
1 1 a
2 1 b
3 2 c

Is there a pure SQL way of contactenating str values into a single string,
and get instead:

id fk str
---------------
1 1 a/b
2 12 c

I was thinking of something like:

SELECT id, fk, CONCAT(str || '/')
FROM FOO
GROUP BY fk

Do we have to use PL/PGSQL for that?

Thanks,

----------------------------------
Philippe Lang, Ing. Dipl. EPFL
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel: +41 (26) 422 13 75
Fax: +41 (26) 422 13 76
Email: philippe.lang@attiksystem.ch

A. Kretschmer

2006-02-09, 7:24 am

am 09.02.2006, um 10:35:03 +0100 mailte Philippe Lang folgendes:
> id fk str
> ---------------
> 1 1 a
> 2 1 b
> 3 2 c
>
> Is there a pure SQL way of contactenating str values into a single string,
> and get instead:
>
> id fk str
> ---------------
> 1 1 a/b
> 2 12 c
>
> I was thinking of something like:
>
> SELECT id, fk, CONCAT(str || '/')
> FROM FOO
> GROUP BY fk
>
> Do we have to use PL/PGSQL for that?

Yes, you need a new aggregate function.
http://www.postgresql.org/docs/8.0/...tive/xaggr.html

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links