Thread: Table Pivot

How do you do a table Pivot in PostgreSQL?

tia,
.V


=====

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

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

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

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