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@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 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
