Hi,
I found this to create dynamic crosstabs (where the resulting columns
are not known beforehand): http://www.ledscripts.com/tech/article/view/5.html
(Thanks for Denis Bitouzé on
http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html
for pointing it out.).
This is basically dynamically generating an SQL string with CASE ...
WHEN that will create a view.
This could work although for hundreds of columns it looks a bit scary
for me.
Isn't there a more elegant way to achieve this with tablefunc crosstab
and if there isn't don't you think it could/should be there?
There is a syntax where you could specify the columns with a SELECT
DISTINCT statement - couldn't it also generate the enumeration string
eg. presuming that all returning colums are stored as text?
Or if that is not possible instead of the enumeration part wouldn't it
be better to put a name of the view that could be created/recreated?
I know that most db people don't care much about pivot/crosstab in the
db but imagine this situation:
I am storing questionnaire results on people. Since the questionnaires
are created by users I have no other way than using an EAV model like
personID, questionID, responseValue to store responses. Now this table
gets long 300 question per questionnaire, 3000 people and we have 1m
row. Now whenever I need to download this data in my case 2/3rd of it
would be redundant if I could pivot it first - and in a 20MB csv its
significant (I know its a tradeoff between processing and storage).
Moreover my users can't do anything with this dataformat - they need
to pivot it offline anyway, which is not easy (Excel cant do it,
Access cant do it, numberGo cant do it for different reasons).
Although the application could do it I think this is a generic
functionality that the database is more suited for.
Please let me know if you know of a good db based way to create a
dynamic crosstab in Postgres - or why there shouldn't be one.
Thanks and regards.
SWK
