From: Julian Scarfe <julian.scarfe@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Concatenate aggregate?
Date: Tue, 6 Aug 2002 09:03:21 +0100
Message-ID: <005201c23d1f$bb4f6ba0$0500a8c0@Wilbur> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: julian.scarfe@ntlworld.com
Subject: Re: Concatenate aggregate?
In-Reply-To: <005201c23d1f$bb4f6ba0$0500a8c0@Wilbur>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox