> > 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-C > rossTab-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 Have you tried this crosstab? http://www.postgresql.org/docs/8.3/interactive/tablefunc.html ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
