Обсуждение: How do I concatenate row-wise instead of column-wise?

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 ; aaa2  | ccc ; ccc3  | ddd ; ddd4  | 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

On Tuesday 16 Jul 2002 9:09 am, Marcus Claesson wrote:
> I have a table like this:
> SELECT * FROM old_tab;
> id    |    descr
> -------------------
> 3    |    ddd
> 3    |    ddd
> 3    |    eee
> 3    |    fff

> SELECT * FROM new_tab;
> id    |    descr
> --------------------------
> 1    |    aaa;bbb
> 2    |    ccc;bbb
> 3    |    ddd;eee;fff
> 4    |    bbb

You'll want to write your own aggregate function - something like max() which
will work over a range of values. This is easier than you might think.

The only thing to be careful of is that order isn't guaranteed, so by default
you could have "ddd;eee;fff" or "fff;ddd;eee" etc.

Go to techdocs.postgresql.org and check the "Postgresql Cookbook" courtesy of
Roberto Mello and also the "Postgresql Notes" by me. Also check the online
manual and the mailing archives (try searching on "aggregate" and "catenate"
or "concat").

HTH

- Richard Huxton

Hi Marcus,

It is simple ,

you need to write a function  and define an aggregate using that function.

in case you have already searched for
the solution and not found here  it is from this mailing list only:

===========================================================================
Date: Tue, 14 May 2002 18:13:09 +0200
From: Mathieu Arnold <mat@mat.cc>
To: pgsql-sql@postgresql.org
Subject: [SQL] aggregate...
Message-ID: <1729482965.1021399989@andromede.reaumur.absolight.net>
X-Mailer: Mulberry/2.2.1 (Win32)
X-wazaaa: True, true
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org
Status: RO
X-Status: O

Hi

I have this :

CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1           WHEN $1 IS NULL OR $1 = '''' THEN $2           ELSE $1 ||
'','' || $2      END 
' LANGUAGE 'sql';


CREATE AGGREGATE list ( BASETYPE = text, SFUNC = comma_cat, STYPE = text,
INITCOND = '' );

I can use it as :
select user, list(email) from user join email using (id_user);

user   | list
-------+-----------------------------
mat    | mat@mat.cc, mat@absolight.fr
isa    | isa@mat.cc

===============================================================

regds

On Tuesday 16 July 2002 13:39, you wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.