Re: dynamic crosstab

Hi,

SunWuKung wrote:
> Hi,
>
> I found this to create dynamic crosstabs (where the resulting columns
...
> This could work although for hundreds of columns it looks a bit scary
> for me.

Well I'd say hundreds of columns are always scary, no matter how you do
it :-)

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

What about not pivoting it? You can run your analysis directly
against your database.

> Although the application could do it I think this is a generic
> functionality that the database is more suited for.

Well after all you want a CSV not a table. You could shortcut this
with a generic query which creates array out of your "columns"
and join them to a CSV line. This would just be outputted as
one single column from database.

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

See above :-)

Regards
Tino