Thread: "Flattening" query result into columns

"Flattening" query result into columns

From

"Thomas Borg Salling"

Date:

22 March 2005, 00:57:34

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">I am looking for a way to ”flatten” a query result, so that rows are ”transposed” into
columns,just as asked here for oracle:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><a
href="http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com">http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com</a></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">Is there any way to do this with pgsql  ?</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">/Thomas.</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font></div>

Re: "Flattening" query result into columns

From

"Sean Davis"

Date:

22 March 2005, 02:44:31

Thomas,

You probably want a crosstab. There is a contributed module in contrib/crosstab. If you do a search of the postgres mailing lists, there will be several posts relating to the same issue.

Sean

----- Original Message -----
From: Thomas Borg Salling
To: pgsql-sql@postgresql.org
Sent: Monday, March 21, 2005 4:57 PM
Subject: [SQL] "Flattening" query result into columns

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 ?

Thanks,
/Thomas.

Re: "Flattening" query result into columns

From

PFC

Date:

22 March 2005, 03:35:28

You could use the array_accum aggregate. See examples in the docs.

On Mon, 21 Mar 2005 22:57:13 +0100, Thomas Borg Salling <tbs@navicon.dk>  
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
> <http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:e
> n-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com>
> &lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.040129232
> 2.7b6c320b%40posting.google.com
>
>
> Is there any way to do this with pgsql  ?
>
>
> Thanks,
>
> /Thomas.
>
>
>

Re: "Flattening" query result into columns

From

"Leif B. Kristensen"

Date:

22 March 2005, 03:40:14

On Monday 21 March 2005 22: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:
>
> Is there any way to do this with pgsql  ?

Just to help out the guys, here's a working link:

<http://groups.google.dk/groups?hl=da&selm=aad10be0.0401292322.7b6c320b%40posting.google.com>

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

Re: "Flattening" query result into columns

From

Scott Marlowe

Date:

22 March 2005, 03:46:47

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.

Re: "Flattening" query result into columns

From

"Tambet Matiisen"

Date:

23 March 2005, 11:13:30

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

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

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]
> Sent: Tuesday, March 22, 2005 12:37 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: "Flattening" query result into columns
>
>
> On Monday 21 March 2005 22: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:
> >
> > Is there any way to do this with pgsql  ?
>
> Just to help out the guys, here's a working link:
>
> <http://groups.google.dk/groups?hl=da&selm=aad10be0.0401292322
.7b6c320b%40posting.google.com>

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

Re: "Flattening" query result into columns

From

"Thomas Borg Salling"

Date:

28 March 2005, 01:10:21

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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
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.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-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 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.

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