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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
