Received: from beppe.angiogenetics.se (beppe.angiogenetics.se [130.242.57.154]) by postgresql.org (Postfix) with ESMTP id 99522474E71 for ; Tue, 16 Jul 2002 04:11:29 -0400 (EDT) Received: from angiogenetics.se (dunderklumpen.angiogenetics.se [130.242.57.150]) by beppe.angiogenetics.se (8.11.6/8.11.6) with ESMTP id g6G8EM123444 for ; Tue, 16 Jul 2002 10:14:22 +0200 Message-ID: <3D33D4AA.1020600@angiogenetics.se> Date: Tue, 16 Jul 2002 10:09:14 +0200 From: Marcus Claesson User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.4.1) Gecko/20020314 Netscape6/6.2.2 X-Accept-Language: en-us MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: How do I concatenate row-wise instead of column-wise? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200207/233 X-Sequence-Number: 8611 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