From: "Thomas Borg Salling" <tbs@navicon.dk>
To: <pgsql-sql@postgresql.org>
Subject: Re: "Flattening" query result into columns
Date: Sun, 27 Mar 2005 23:09:50 +0200
Organization: NAVICON A/S
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-reply-to: <1111452377.26897.487.camel@state.g2switchworks.com>
Thread-Index: AcUueUj3AqtKG4ptQyKBhUdUZ9oUugDuj9SQ
Message-Id: <20050327211004.031F0533F0@svr1.postgresql.org>

Thanks to all for the useful replies. I chose this approach from Scott
Marlowe, which can meet the requirements I work against. Arrays seemed =
to
have some issues with element with null elements.
/Thomas.
=20
-----Oprindelig meddelelse-----
Fra: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]
P=E5 vegne af Scott Marlowe
Sendt: 22. marts 2005 01:46
Til: Thomas Borg Salling
Cc: pgsql-sql@postgresql.org
Emne: Re: [SQL] "Flattening" query result into columns

On Mon, 2005-03-21 at 15:57, Thomas Borg Salling wrote:
> I am looking for a way to =94flatten=94 a query result, so that rows =
are
> =94transposed=94 into columns, just as asked here for oracle:
>=20
>
http://groups.google.dk/groups?hl=3Dda&lr=3D&client=3Dfirefox-a&rls=3Dorg=
.mozilla:en
-US:official&selm=3Daad10be0.0401292322.7b6c320b%40posting.google.com
>=20
> =20
>=20
> Is there any way to do this with pgsql  ?

Here's one from work that allows you to do the same basic thing without
a separate cross table:

select=20
	a.lt ,
	b.perspective as XYZ_pers,
	b.averageresponsetime as XYZ_aver,
	b.lowestresponsetime as XYZ_lowe,
	b.highestresponsetime as XYZ_high,
	b.totalcount as XYZ_tota,
	c.perspective as ABC_pers,
	c.averageresponsetime as ABC_aver,
	c.lowestresponsetime as ABC_lowe,
	c.highestresponsetime as ABC_high,
	c.totalcount as ABC_tota=20
from (
	select distinct date_trunc('minutes', lastflushtime) as lt from
businessrequestsummary
	where lastflushtime between '2005-03-14 18:42:34' and '2005-03-21
18:42:34'=20
	and perspective in ('XYZ','ABC')
) as a=20
left join (
	select date_trunc('minutes', lastflushtime) as lt,
	max(perspective) as perspective,
	floor(avg(averageresponsetime)) as averageresponsetime,
	min(lowestresponsetime) as lowestresponsetime,
	max(highestresponsetime) as highestresponsetime,
	sum(totalcount) as totalcount
	from businessrequestsummary=20
	where perspective =3D'XYZ'
	group by date_trunc('minutes', lastflushtime)
) as b=20
on=20
	(a.lt=3Db.lt)=20
left join (
	select date_trunc('minutes', lastflushtime) as lt,
	max(perspective) as perspective,
	floor(avg(averageresponsetime)) as averageresponsetime,
	min(lowestresponsetime) as lowestresponsetime,
	max(highestresponsetime) as highestresponsetime,
	sum(totalcount) as totalcount
	from businessrequestsummary=20
	where perspective =3D'ABC'
	group by date_trunc('minutes', lastflushtime)
) as c=20
on=20
	(a.lt=3Dc.lt)=20

IT's generated by a script that makes it as big as we need for all the
different perspectives.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings