Thread: Table Pivot

Table Pivot

From
"V. Cekvenich"
Date:
How do you do a table Pivot in PostgreSQL?

tia,
.V


=====



Re: Table Pivot

From
Christoph Haller
Date:
>
> 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




Re: Table Pivot

From
Gary Stainburn
Date:
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



Re: Table Pivot

From
Joe Conway
Date:
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



Re: Table Pivot

From
"Vic Cekvenich"
Date:

> > 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


=====

Re: Table Pivot

From
"Vic Cekvenich"
Date:
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
>
>

Re: Table Pivot

From
Joe Conway
Date:
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