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@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:
> 
> 

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/