From: | Archibald Zimonyi <archie(at)netg(dot)se> |
---|---|
To: | Julian Scarfe <julian(dot)scarfe(at)ntlworld(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Concatenate aggregate? |
Date: | 2002-08-06 08:28:37 |
Message-ID: | Pine.LNX.4.44.0208061027250.8514-100000@elvegris.netg.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Julian Scarfe | 2002-08-06 08:37:16 | Re: Concatenate aggregate? |
Previous Message | Julian Scarfe | 2002-08-06 08:03:21 | Concatenate aggregate? |