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/
