Re: [SQL] "Flattening" query result into columns

[Thomas Borg Salling]

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.
 
-----Oprindelig meddelelse-----
Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
På 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 ”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.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.

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend