How do I concatenate row-wise instead of column-wise?

From: Marcus Claesson <marcus(dot)claesson(at)angiogenetics(dot)se>
To: pgsql-sql(at)postgresql(dot)org
Subject: How do I concatenate row-wise instead of column-wise?
Date: 2002-07-16 08:09:14
Message-ID: 3D33D4AA.1020600@angiogenetics.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Luis Alberto Amigo Navarro 2002-07-16 08:51:03 Re: [HACKERS] please help on query
Previous Message Thomas Lockhart 2002-07-16 06:10:29 Re: [SQL] line datatype