From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Thomas Borg Salling <tbs@navicon.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: "Flattening" query result into columns
Date: Mon, 21 Mar 2005 18:46:17 -0600
Message-ID: <1111452377.26897.487.camel@state.g2switchworks.com> (raw)
In-Reply-To: <20050321215723.E79E356388@svr1.postgresql.org>
References: <20050321215723.E79E356388@svr1.postgresql.org>
On Mon, 2005-03-21 at 15: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:
>
> http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:en-US:official...
>
>
>
> 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
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
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'
and perspective in ('XYZ','ABC')
) as a
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
where perspective ='XYZ'
group by date_trunc('minutes', lastflushtime)
) as b
on
(a.lt=b.lt)
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
where perspective ='ABC'
group by date_trunc('minutes', lastflushtime)
) as c
on
(a.lt=c.lt)
IT's generated by a script that makes it as big as we need for all the different perspectives.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, tbs@navicon.dk
Subject: Re: "Flattening" query result into columns
In-Reply-To: <1111452377.26897.487.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox