On Feb 14, 2008, at 2:04 AM, Balázs Klein wrote:
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.
As of versions >= 8.2 you can store NULL values in arrays. Perhaps
you could have a Question -> Index table and then use an array per
person for their answers.
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.
Have a look at http://www.varlena.com/varlena/GeneralBits/110.php for
a totally different approach to questionnaires.
Erik Jones
DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
