Re: dynamic crosstab - Mailing list pgsql-general

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

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
thequery are exactly the same (there wouldn’t be empty cells in a crosstab) - it’s very useful for some cases but in
generalthat 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)
modelyou have no generic or way of going back to the usual one entity per row model. This is something that Access has
beenable to do (up to 255 columns) as far as I can remember. When I google about this topic I find that the majority of
peopleare still referring to that solution as the easiest for this purpose. Tablefunc crosstab is so close to a good
solutionfor this with the syntax where you could specify the columns with a query - the only shortcoming is that you
stillhave 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@wildenhain.de]
Sent: Wednesday, February 13, 2008 2:05 PM
To: SunWuKung
Cc: pgsql-general@postgresql.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