pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"Flattening" query result into columns
7+ messages / 6 participants
[nested] [flat]
* "Flattening" query result into columns
@ 2005-03-21 21:57 Thomas Borg Salling <tbs@navicon.dk>
0 siblings, 4 replies; 7+ messages in thread
From: Thomas Borg Salling @ 2005-03-21 21:57 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-21 22:37 Leif B. Kristensen <leif@solumslekt.org>
parent: Thomas Borg Salling <tbs@navicon.dk>
3 siblings, 0 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-03-21 22:37 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-21 23:27 Sean Davis <sdavis2@mail.nih.gov>
parent: Thomas Borg Salling <tbs@navicon.dk>
3 siblings, 0 replies; 7+ messages in thread
From: Sean Davis @ 2005-03-21 23:27 UTC (permalink / raw)
To: Thomas Borg Salling <tbs@navicon.dk>; pgsql-sql
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...
Is there any way to do this with pgsql ?
Thanks,
/Thomas.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-22 00:35 PFC <lists@boutiquenumerique.com>
parent: Thomas Borg Salling <tbs@navicon.dk>
3 siblings, 0 replies; 7+ messages in thread
From: PFC @ 2005-03-22 00:35 UTC (permalink / raw)
To: Thomas Borg Salling <tbs@navicon.dk>; pgsql-sql
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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-22 00:46 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Thomas Borg Salling <tbs@navicon.dk>
3 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2005-03-22 00:46 UTC (permalink / raw)
To: Thomas Borg Salling <tbs@navicon.dk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-23 08:13 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 7+ messages in thread
From: Tambet Matiisen @ 2005-03-23 08:13 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: "Flattening" query result into columns
@ 2005-03-27 21:09 Thomas Borg Salling <tbs@navicon.dk>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 7+ messages in thread
From: Thomas Borg Salling @ 2005-03-27 21:09 UTC (permalink / raw)
To: pgsql-sql
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.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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-03-27 21:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-21 21:57 "Flattening" query result into columns Thomas Borg Salling <tbs@navicon.dk>
2005-03-21 22:37 ` Leif B. Kristensen <leif@solumslekt.org>
2005-03-21 23:27 ` Sean Davis <sdavis2@mail.nih.gov>
2005-03-22 00:35 ` PFC <lists@boutiquenumerique.com>
2005-03-22 00:46 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-27 21:09 ` Thomas Borg Salling <tbs@navicon.dk>
2005-03-23 08:13 Re: "Flattening" query result into columns Tambet Matiisen <t.matiisen@aprote.ee>
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