Re: dynamic crosstab

Hi,
ye, hundreds of columns - but there is no helping it, that’s the way many questionnaire are and the representation of the responses (when not in a database) is always one person per row. I would need this for exporting, but also to show results online.

Although it’s a good idea I am afraid that an array could only help me when the info I store about all the persons in the query are exactly the same (there wouldn’t be empty cells in a crosstab) - it’s very useful for some cases but in general that sounds like a dangerous presumption for me.

I think this is a generic shortcoming of Postgres - whenever you are forced to create an EAV (Entity-Attribute-Value) model you have no generic or way of going back to the usual one entity per row model. This is something that Access has been able to do (up to 255 columns) as far as I can remember. When I google about this topic I find that the majority of people are still referring to that solution as the easiest for this purpose. Tablefunc crosstab is so close to a good solution for this with the syntax where you could specify the columns with a query - the only shortcoming is that you still have to enumerate the columns and their datatype. I always hope that somebody might have something similar but generic - eg. create those columns automatically and just treat them all as text.

Regards,
SWK

-----Original Message-----
From: Tino Wildenhain [mailto:tino(at)wildenhain(dot)de]
Sent: Wednesday, February 13, 2008 2:05 PM
To: SunWuKung
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.0/1137 - Release Date: 11/18/2007 5:15 PM