Search Postgresql Archives

Re: dynamic crosstab

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux