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

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