From: Marcus Claesson <marcus.claesson@angiogenetics.se>
To: pgsql-sql@postgresql.org
Subject: How do I concatenate row-wise instead of column-wise?
Date: Tue, 16 Jul 2002 10:09:14 +0200
Message-ID: <3D33D4AA.1020600@angiogenetics.se> (raw)
I have a table like this:
SELECT * FROM old_tab;
id | descr
-------------------
1 | aaa
1 | aaa
1 | bbb
2 | ccc
2 | bbb
3 | ddd
3 | ddd
3 | eee
3 | fff
4 | bbb
etc...
And I want a new table where the descr is concatenated row-wise like this:
SELECT * FROM new_tab;
id | descr
--------------------------
1 | aaa;bbb
2 | ccc;bbb
3 | ddd;eee;fff
4 | bbb
etc...
This is the closest I get....:
UPDATE new_tab SET descr = old_tab.descr||' ; '||old_tab.descr from
old_tab where old_tab.id=new_tab.id;
UPDATE 4
SELECT * FROM new_tab ;
id | descr
----+-----------
1 | aaa ; aaa
2 | ccc ; ccc
3 | ddd ; ddd
4 | bbb ; bbb
etc...
Thus, the concatenating operator never works on other rows than the
present. How can I get around that and still stick to the postgresql syntax?
Regards
Marcus
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: marcus.claesson@angiogenetics.se
Subject: Re: How do I concatenate row-wise instead of column-wise?
In-Reply-To: <3D33D4AA.1020600@angiogenetics.se>
* 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