Re: dynamic crosstab

Yes, thanks.
The problem with those function is that they all have an AS (columname type,
...) part or equivalent.

-----Original Message-----
From: Masse Jacques [mailto:jacques.masse@bordeaux.cemagref.fr] 
Sent: Wednesday, February 13, 2008 10:20 AM
To: SunWuKung; pgsql-general@postgresql.org
Subject: RE: [GENERAL] dynamic crosstab


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