content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-4"
Content-Transfer-Encoding: quoted-printable
Subject: Re: "Flattening" query result into columns
Date: Wed, 23 Mar 2005 10:13:14 +0200
Message-ID: <A66A11DBF5525341AEF6B8DE39CDE77008805F@black.aprote.com>
Thread-Topic: "Flattening" query result into columns
Thread-Index: AcUvgClN4YkAW8nEQ+2FKrExmwhnZQ==
From: "Tambet Matiisen" <t.matiisen@aprote.ee>
To: "Leif B. Kristensen" <leif@solumslekt.org>,
	<pgsql-sql@postgresql.org>

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 =3D '''' then $2 else $1 || '' '' || $2 end
';

drop aggregate concat(text) cascade;
create aggregate concat=20
(
   basetype =3D text,
   stype =3D text,
   sfunc =3D concat,
   initcond =3D ''
);

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]=20
> Sent: Tuesday, March 22, 2005 12:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: "Flattening" query result into columns
>=20
>=20
> On Monday 21 March 2005 22:57, Thomas Borg Salling wrote:
> > I am looking for a way to "flatten" a query result, so that=20
> rows are=20
> > "transposed" into columns, just as asked here for oracle:
> >
> > Is there any way to do this with pgsql =A0?
>=20
> Just to help out the guys, here's a working link:
>=20
> <http://groups.google.dk/groups?hl=3Dda&selm=3Daad10be0.0401292322
.7b6c320b%40posting.google.com>

What you're asking for is called a pivot table, and at least in Oracle=20
they use a function called decode(). I need exactly the same thing, but=20
I too am unable to find the optimal way to do it in PostgreSQL.
--=20
Leif Biberg Kristensen
http://solumslekt.org/