From: Tambet Matiisen <t.matiisen@aprote.ee>
To: Leif B. Kristensen <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: "Flattening" query result into columns
Date: Wed, 23 Mar 2005 10:13:14 +0200
Message-ID: <A66A11DBF5525341AEF6B8DE39CDE77008805F@black.aprote.com> (raw)
The problem in linked article could be solved with custom aggregate in PostgreSQL:
create or replace function concat(text, text) returns text
immutable
language sql
as '
select case when $1 = '''' then $2 else $1 || '' '' || $2 end
';
drop aggregate concat(text) cascade;
create aggregate concat
(
basetype = text,
stype = text,
sfunc = concat,
initcond = ''
);
The query:
select Col_A, concat(Col_B)
from table
group by Col_A
If you want only distinct values concatenated:
select Col_A, concat(distinct Col_B)
from table
group by Col_A
Tambet
> -----Original Message-----
> From: Leif B. Kristensen [mailto:leif@solumslekt.org]
> Sent: Tuesday, March 22, 2005 12:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: "Flattening" query result into columns
>
>
> On Monday 21 March 2005 22:57, Thomas Borg Salling wrote:
> > I am looking for a way to "flatten" a query result, so that
> rows are
> > "transposed" into columns, just as asked here for oracle:
> >
> > Is there any way to do this with pgsql ?
>
> Just to help out the guys, here's a working link:
>
> <http://groups.google.dk/groups?hl=da&selm=aad10be0.0401292322.7b6c320b%40posting.google.com>
What you're asking for is called a pivot table, and at least in Oracle
they use a function called decode(). I need exactly the same thing, but
I too am unable to find the optimal way to do it in PostgreSQL.
--
Leif Biberg Kristensen
http://solumslekt.org/
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: t.matiisen@aprote.ee, leif@solumslekt.org
Subject: Re: "Flattening" query result into columns
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE77008805F@black.aprote.com>
* 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