Message-ID: <005201c23d1f$bb4f6ba0$0500a8c0@Wilbur>
From: "Julian Scarfe" <julian.scarfe@ntlworld.com>
To: <pgsql-sql@postgresql.org>
Subject: Concatenate aggregate?
Date: Tue, 6 Aug 2002 09:03:21 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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