Re: dynamic crosstab - Mailing list pgsql-general

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.

-----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

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