How to create a pivot table in PostgreSQL?

[metaltree]

Suppose I have the 'abc' table:

individual year size
1_4_5 1 112
1_4_5 2 123
1_4_5 3 146
1_4_5 4 157
2_8_3 1 92
2_8_3 2 104
2_8_3 3 98
...

I want to pivot the table to get:

individual size_yr1 size_yr2 size_yr3 ...
1_4_5 112 123 146
2_8_3 92 104 98
...

In Access, you would do:

TRANSFORM Avg(size) AS size_avg
SELECT abc.individual
FROM abc
GROUP BY abc.individual
PIVOT abc.year;

But this is MS-specific SQL...

Is there a simple way to do it in PostgreSQL?

 

[postgresql007]

You can use a function called "crosstab" to do that.
It is part of the PostgreSQL contrib-section and it has to be installed separately. Checkout the "contrib/tablefunc" directory inside your sourceball.


Hans-Jürgen Schönig
Cybertec Schönig & Schönig GmbH
URL:

http://www.postgresql-support.de

PostgreSQL Support, Training, Replication