Re: dynamic crosstab - Mailing list pgsql-general

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