pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I concatenate row-wise instead of column-wise?
3+ messages / 3 participants
[nested] [flat]
* How do I concatenate row-wise instead of column-wise?
@ 2002-07-16 08:09 Marcus Claesson <marcus.claesson@angiogenetics.se>
0 siblings, 2 replies; 3+ messages in thread
From: Marcus Claesson @ 2002-07-16 08:09 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How do I concatenate row-wise instead of column-wise?
@ 2002-07-16 09:15 Richard Huxton <dev@archonet.com>
parent: Marcus Claesson <marcus.claesson@angiogenetics.se>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2002-07-16 09:15 UTC (permalink / raw)
To: Marcus Claesson <marcus.claesson@angiogenetics.se>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How do I concatenate row-wise instead of column-wise?
@ 2002-07-16 11:34 Rajesh Kumar Mallah. <mallah@trade-india.com>
parent: Marcus Claesson <marcus.claesson@angiogenetics.se>
1 sibling, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-16 11:34 UTC (permalink / raw)
To: Marcus Claesson <marcus.claesson@angiogenetics.se>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-07-16 11:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-16 08:09 How do I concatenate row-wise instead of column-wise? Marcus Claesson <marcus.claesson@angiogenetics.se>
2002-07-16 09:15 ` Richard Huxton <dev@archonet.com>
2002-07-16 11:34 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
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