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&selm=aad10be0.0401292322.7b6c320b%40posting.google.com
>
>
>
> 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.highestresponsetimeas XYZ_high,b.totalcount as XYZ_tota,c.perspective as ABC_pers,c.averageresponsetime as
ABC_aver,c.lowestresponsetimeas ABC_lowe,c.highestresponsetime as ABC_high,c.totalcount as ABC_tota
from (select distinct date_trunc('minutes', lastflushtime) as lt from businessrequestsummarywhere lastflushtime between
'2005-03-1418: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 totalcountfrom
businessrequestsummarywhere 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 totalcountfrom
businessrequestsummarywhere 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.