pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Pivot
9+ messages / 6 participants
[nested] [flat]
* Table Pivot
@ 2003-02-13 10:48 V. Cekvenich <vc@basebeans.com>
0 siblings, 1 reply; 9+ messages in thread
From: V. Cekvenich @ 2003-02-13 10:48 UTC (permalink / raw)
To: pgsql-sql
How do you do a table Pivot in PostgreSQL?
tia,
.V
=====
^ permalink raw reply [nested|flat] 9+ messages in thread
* table Pivot
@ 2003-02-13 16:45 V. Cekvenich <netsql@yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: V. Cekvenich @ 2003-02-13 16:45 UTC (permalink / raw)
To: pgsql-sql
How do you do a table Pivot in PostgreSQL?
tia,
.V
=====
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-14 09:48 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Haller @ 2003-02-14 09:48 UTC (permalink / raw)
To: vc@basebeans.com; +Cc: pgsql-sql
>
> How do you do a table Pivot in PostgreSQL?
>
I'll post you this old mail:
> > I saw something that might somewhat a bit more
> > flexible solution using SQL. I don't know if it works
> > in PostgreSQL. I saw it at the MySQL site.
> >
> > The following is the URL:
> > http://www.mysql.com/articles/wizard/index.html
> >
> > Has anyone tried this on a PostgreSQL database ?
Actually, I'm rather annoyed with the article author. He first claims
that Joe Celko reccommends expensive add-on software for crosstabs (Joe
does not) and then goes on to use one of Joe's own solutions. However,
given the author's thouroughness otherwise, I'm sure the innaccuracy is
due to some kind of misunderstanding.
There are, in fact, 3 simple SQL-based solutions to the crosstab
problem. Which one you use depends on the shape of your data. I am
not going to cover them in detail here (I'll save that for an article)
but to sum up:
1) The SUM(CASE()) statement method, as outlined in the article, which
is good for crosstabs expecting small numbers of columns. Or, in the
case of this article, good for RDBMS which do not support subselects.
2) The LEFT JOIN + Sub-Select method, which is good for crosstabs with
lots of columns but not that many rows in the crosstabbed table.
3) The "crosstab reference grid" method, which is good for large tables
and crosstabs with lots of columns, but requires setup and maintainence
by trigger.
Joe Celko covers these 3 types in "SQL for Smarties". I will write
a PostgreSQL implementation in a later article.
-Josh Berkus
I have an extended example using the 1) method implemented in plpgsql.
Let me know if you want to have a look at it.
Regards, Christoph
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-14 12:02 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: V. Cekvenich <vc@basebeans.com>
0 siblings, 0 replies; 9+ messages in thread
From: Gary Stainburn @ 2003-02-14 12:02 UTC (permalink / raw)
To: vc@basebeans.com; pgsql-sql
On Thursday 13 Feb 2003 10:48 am, V. Cekvenich wrote:
> How do you do a table Pivot in PostgreSQL?
Hi, I've noticed that you've posted this twice now with no response.
I can't help you because I don't know what you mean by doing a 'table pivot',
but maybe if you describe what you're trying to do I'll see if I can help
Gary
>
> tia,
> .V
>
>
> =====
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-14 15:35 Joe Conway <mail@joeconway.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 9+ messages in thread
From: Joe Conway @ 2003-02-14 15:35 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: vc@basebeans.com; pgsql-sql
Christoph Haller wrote:
>
> I have an extended example using the 1) method implemented in plpgsql.
> Let me know if you want to have a look at it.
>
If you're using 7.3.x, and don't mind a function based (vs pure sql
based) approach, take a look at crosstab() in contrib/tablefunc.
It has a limitation in that the data source query must provide for
"missing" rows. In other words, if your query produces:
id1 cat1 val
id1 cat2 val
id2 cat1 val
id2 cat2 val
id2 cat3 val
and you specify 3 catagory columns to the crosstab function, then
crosstab() will not give the result you're probably expecting. I
typically work around that by doing a sub-select that is the
cross-product of (distinct id) and (distinct cat), and then left joining
that to the actual data. That will produce somthing like:
id1 cat1 val
id1 cat2 val
id1 cat3 NULL
id2 cat1 val
id2 cat2 val
id2 cat3 val
For large numbers of rows and columns (at least with my data) I've found
that crosstab() provides a significant performance boost.
HTH,
Joe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: Re: Table Pivot
@ 2003-02-16 16:51 V. Cekvenich <vc@basebeans.com>
0 siblings, 0 replies; 9+ messages in thread
From: V. Cekvenich @ 2003-02-16 16:51 UTC (permalink / raw)
To: pgsql-sql
--- "V. Cekvenich" <vc@basebeans.com> wrote:
> Date: Sun, 16 Feb 2003 08:50:19 -0800 (PST)
> From: "V. Cekvenich" <vc@basebeans.com>
> Subject: Re: [SQL] Table Pivot
> To: Christoph Haller <ch@rodos.fzk.de>
>
> Thanks, I read Rozenshtein's book on Otpimizing
> Advanced SQL, and I am reading page 378 of Celkos
> SQl
> 4 Smarties.
>
> I understand your case 1.
> What is case 2 and 3? Is there a link to find more,
> or
> maybe you can give me a bit more direction please?
>
> Vic
>
> --- Christoph Haller <ch@rodos.fzk.de> wrote:
> > >
> > > How do you do a table Pivot in PostgreSQL?
> > >
> > I'll post you this old mail:
> >
> >
> > > > I saw something that might somewhat a bit more
> > > > flexible solution using SQL. I don't know if
> it
> > works
> > > > in PostgreSQL. I saw it at the MySQL site.
> > > >
> > > > The following is the URL:
> > > >
> > http://www.mysql.com/articles/wizard/index.html
> > > >
> > > > Has anyone tried this on a PostgreSQL
> database
> > ?
> >
> > Actually, I'm rather annoyed with the article
> > author. He first claims
> > that Joe Celko reccommends expensive add-on
> software
> > for crosstabs (Joe
> > does not) and then goes on to use one of Joe's own
> > solutions. However,
> > given the author's thouroughness otherwise, I'm
> sure
> > the innaccuracy is
> > due to some kind of misunderstanding.
> >
> > There are, in fact, 3 simple SQL-based solutions
> to
> > the crosstab
> > problem. Which one you use depends on the shape
> of
> > your data. I am
> > not going to cover them in detail here (I'll save
> > that for an article)
> > but to sum up:
> >
> > 1) The SUM(CASE()) statement method, as outlined
> in
> > the article, which
> > is good for crosstabs expecting small numbers of
> > columns. Or, in the
> > case of this article, good for RDBMS which do not
> > support subselects.
> >
> > 2) The LEFT JOIN + Sub-Select method, which is
> good
> > for crosstabs with
> > lots of columns but not that many rows in the
> > crosstabbed table.
> >
> > 3) The "crosstab reference grid" method, which is
> > good for large tables
> > and crosstabs with lots of columns, but requires
> > setup and maintainence
> > by trigger.
> >
> > Joe Celko covers these 3 types in "SQL for
> > Smarties". I will write
> > a PostgreSQL implementation in a later article.
> >
> > -Josh Berkus
> >
> > I have an extended example using the 1) method
> > implemented in plpgsql.
> > Let me know if you want to have a look at it.
> >
> > Regards, Christoph
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
> =====
>
>
=====
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-16 17:12 Vic Cekvenich <maillist@basebeans.com>
0 siblings, 1 reply; 9+ messages in thread
From: Vic Cekvenich @ 2003-02-16 17:12 UTC (permalink / raw)
To: pgsql-sql
> > I have an extended example using the 1) method
> implemented in plpgsql.
> > Let me know if you want to have a look at it.
> >
>
> If you're using 7.3.x, and don't mind a function
> based (vs pure sql
> based) approach, take a look at crosstab() in
> contrib/tablefunc.
I looked but could not find.
Where Can I find this?
>
> It has a limitation in that the data source query
> must provide for
> "missing" rows. In other words, if your query
> produces:
>
> id1 cat1 val
> id1 cat2 val
> id2 cat1 val
> id2 cat2 val
> id2 cat3 val
>
> and you specify 3 catagory columns to the crosstab
> function, then
> crosstab() will not give the result you're probably
> expecting. I
> typically work around that by doing a sub-select
> that is the
> cross-product of (distinct id) and (distinct cat),
> and then left joining
> that to the actual data. That will produce somthing
> like:
>
> id1 cat1 val
> id1 cat2 val
> id1 cat3 NULL
> id2 cat1 val
> id2 cat2 val
> id2 cat3 val
>
> For large numbers of rows and columns (at least with
> my data) I've found
> that crosstab() provides a significant performance
> boost.
Cool, if I can find a link to compiling the Crosstab
function.
tia,
.V
>
> HTH,
>
> Joe
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
=====
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-16 17:14 Vic Cekvenich <maillist@basebeans.com>
0 siblings, 0 replies; 9+ messages in thread
From: Vic Cekvenich @ 2003-02-16 17:14 UTC (permalink / raw)
To: pgsql-sql
Thanks, I read Rozenshtein's book on Otpimizing
Advanced SQL, and I am reading page 378 of Celkos SQl
4 Smarties.
I understand your case 1.
What is case 2 and 3? Is there a link to find more, or
maybe you can give me a bit more direction please?
Vic
--- Christoph Haller <ch@rodos.fzk.de> wrote:
> >
> > How do you do a table Pivot in PostgreSQL?
> >
> I'll post you this old mail:
>
>
> > > I saw something that might somewhat a bit more
> > > flexible solution using SQL. I don't know if it
> works
> > > in PostgreSQL. I saw it at the MySQL site.
> > >
> > > The following is the URL:
> > >
> http://www.mysql.com/articles/wizard/index.html
> > >
> > > Has anyone tried this on a PostgreSQL database
> ?
>
> Actually, I'm rather annoyed with the article
> author. He first claims
> that Joe Celko reccommends expensive add-on software
> for crosstabs (Joe
> does not) and then goes on to use one of Joe's own
> solutions. However,
> given the author's thouroughness otherwise, I'm sure
> the innaccuracy is
> due to some kind of misunderstanding.
>
> There are, in fact, 3 simple SQL-based solutions to
> the crosstab
> problem. Which one you use depends on the shape of
> your data. I am
> not going to cover them in detail here (I'll save
> that for an article)
> but to sum up:
>
> 1) The SUM(CASE()) statement method, as outlined in
> the article, which
> is good for crosstabs expecting small numbers of
> columns. Or, in the
> case of this article, good for RDBMS which do not
> support subselects.
>
> 2) The LEFT JOIN + Sub-Select method, which is good
> for crosstabs with
> lots of columns but not that many rows in the
> crosstabbed table.
>
> 3) The "crosstab reference grid" method, which is
> good for large tables
> and crosstabs with lots of columns, but requires
> setup and maintainence
> by trigger.
>
> Joe Celko covers these 3 types in "SQL for
> Smarties". I will write
> a PostgreSQL implementation in a later article.
>
> -Josh Berkus
>
> I have an extended example using the 1) method
> implemented in plpgsql.
> Let me know if you want to have a look at it.
>
> Regards, Christoph
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Table Pivot
@ 2003-02-17 04:02 Joe Conway <mail@joeconway.com>
parent: Vic Cekvenich <maillist@basebeans.com>
0 siblings, 0 replies; 9+ messages in thread
From: Joe Conway @ 2003-02-17 04:02 UTC (permalink / raw)
To: Vic Cekvenich <maillist@basebeans.com>; +Cc: pgsql-sql
Vic Cekvenich wrote:
>>based) approach, take a look at crosstab() in
>>contrib/tablefunc.
>
> I looked but could not find.
> Where Can I find this?
>
[...snip...]
>
> Cool, if I can find a link to compiling the Crosstab
> function.
Well, as I said above, it's in contrib/tablefunc. First off, it is only
available in PostgreSQL 7.3.x. If you are installing from source, start
at the top of the postgres source tree and type:
cd contrib/tablefunc
make
make install
See README.tablefunc for more details, and
contrib/tablefunc/expected/tablefunc.out
as a source of examples.
If you are installing from binary, look for where "contrib" is
installed. For example, if installing from RPM, make sure you have the
postgresql-contrib package installed.
HTH,
Joe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-02-17 04:02 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-13 10:48 Table Pivot V. Cekvenich <vc@basebeans.com>
2003-02-14 12:02 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-02-13 16:45 table Pivot V. Cekvenich <netsql@yahoo.com>
2003-02-14 09:48 Re: Table Pivot Christoph Haller <ch@rodos.fzk.de>
2003-02-14 15:35 ` Joe Conway <mail@joeconway.com>
2003-02-16 16:51 Fwd: Re: Table Pivot V. Cekvenich <vc@basebeans.com>
2003-02-16 17:12 Re: Table Pivot Vic Cekvenich <maillist@basebeans.com>
2003-02-17 04:02 ` Joe Conway <mail@joeconway.com>
2003-02-16 17:14 Re: Table Pivot Vic Cekvenich <maillist@basebeans.com>
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