On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:
-----Original Message-----
From: Erik Jones [mailto:erik@xxxxxxxxxx]
Sent: Thursday, February 14, 2008 5:15 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; 'SunWuKung'; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: dynamic crosstab
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
Hi,
the part that I don't know is how to put those NULLs in.
It could well be doable I just can't do it myself.
How does the query look like that produces from this input:
PersonID AttributeID Value
1 1 aaa
1 2 bbb
1 3 ccc
2 1 ddd
2 3 eee
this output, without manually enumerating the attributeids:
1 (aaa,bbb,ccc)
2 (ddd,NULL,eee)
Thx.
B.
My point was to get rid of the the EAV setup. Something like:
CREATE TABLE questions (
question_id serial primary key,
question text not null
);
CREATE TABLE people (
person_id serial primary key,
....
);
CREATE TABLE answers (
person_id integer references people,
answers text[]
);
where the indexes into answers are ids from questions. You don't get
any easy foreign keys for those indexes into the questions table,
which you definitely don't have with the EAV setup anyway, but with
this you don't need any kind of pivot/crosstab functionality.
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
