Re: Concatenate aggregate?

One way is to create your own aggregate function. See the reference manual
under CREATE AGGREGATE.

On Tue, 6 Aug 2002, Julian Scarfe wrote:

> I'd like to concatenate text from one field that has been returned by a
> grouped query.
>
> e.g.
>
> create table test (id int, thetext text);
> CREATE
> playj=> insert into test values (1, 'Hello number 1');
> INSERT 697997 1
> playj=> insert into test values (2, 'Hello number 2');
> INSERT 697998 1
> playj=> insert into test values (1, 'Hello again number 1');
> INSERT 697999 1
> playj=> select * from test;
>
>  id |       thetext
> ----+----------------------
>   1 | Hello number 1
>   2 | Hello number 2
>   1 | Hello again number 1
> (3 rows)
>
> I can do:
>
> playj=> select id, max(thetext) from test group by id;
>
>  id |      max
> ----+----------------
>   1 | Hello number 1
>   2 | Hello number 2
> (2 rows)
>
> But what I'd like to do is something like:
>
> playj=> select id, concat(thetext, ' -- ') from test group by id;
>
>  id |      concat
> ----+----------------
>   1 | Hello number 1 -- Hello again number 1
>   2 | Hello number 2
> (2 rows)
>
> [Ordering is unimportant here.]
>
> Any pointers to useful starting points please?
>
> Thanks
>
> Julian Scarfe
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>