From: | Balázs Klein <Balazs(dot)Klein(at)t-online(dot)hu> |
---|---|
To: | "'Erik Jones'" <erik(at)myemma(dot)com> |
Cc: | "'Tino Wildenhain'" <tino(at)wildenhain(dot)de>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dynamic crosstab |
Date: | 2008-02-14 16:56:12 |
Message-ID: | 20080214165614.801E9BE292@mail00d.mail.t-online.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)myemma(dot)com]
Sent: Thursday, February 14, 2008 5:15 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; 'SunWuKung'; pgsql-general(at)postgresql(dot)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(at)myemma(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Jones | 2008-02-14 16:56:26 | Re: How to cope with low disk space |
Previous Message | brrCv | 2008-02-14 16:40:49 | initdb problem with Windows Installer for PostgreSQL 8.2.4 |