Обсуждение: Concatenate strings using GROUP BY

Поиск
Список
Период
Сортировка

Concatenate strings using GROUP BY

От
"Philippe Lang"
Дата:
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

Re: Concatenate strings using GROUP BY

От
"A. Kretschmer"
Дата:
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/interactive/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    ===