Re: dynamic crosstab - Mailing list pgsql-general

On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:

> -----Original Message-----
> From: Erik Jones [mailto:erik@myemma.com]
> Sent: Thursday, February 14, 2008 5:15 PM
> To: Balázs Klein
> Cc: 'Tino Wildenhain'; 'SunWuKung'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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@myemma.com
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