Re: "Flattening" query result into columns

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(at)solumslekt(dot)org]
> Sent: Tuesday, March 22, 2005 12:37 AM
> To: pgsql-sql(at)postgresql(dot)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/