Re: dynamic crosstab
От | Balázs Klein |
---|---|
Тема | Re: dynamic crosstab |
Дата | |
Msg-id | 20080214080411.0E6F62E3B0@mail01d.mail.t-online.hu обсуждение исходный текст |
Ответ на | Re: dynamic crosstab (Tino Wildenhain <tino@wildenhain.de>) |
Ответы |
Re: dynamic crosstab
(Erik Jones <erik@myemma.com>)
Re: dynamic crosstab (Balázs Klein <Balazs.Klein@t-online.hu>) |
Список | pgsql-general |
Hi, ye, hundreds of columns - but there is no helping it, that’s the way many questionnaire are and the representation of theresponses (when not in a database) is always one person per row. I would need this for exporting, but also to show resultsonline. 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 thequery are exactly the same (there wouldn’t be empty cells in a crosstab) - it’s very useful for some cases but in generalthat sounds like a dangerous presumption for me. I think this is a generic shortcoming of Postgres - whenever you are forced to create an EAV (Entity-Attribute-Value) modelyou have no generic or way of going back to the usual one entity per row model. This is something that Access has beenable to do (up to 255 columns) as far as I can remember. When I google about this topic I find that the majority of peopleare still referring to that solution as the easiest for this purpose. Tablefunc crosstab is so close to a good solutionfor this with the syntax where you could specify the columns with a query - the only shortcoming is that you stillhave 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. Regards, SWK -----Original Message----- From: Tino Wildenhain [mailto:tino@wildenhain.de] Sent: Wednesday, February 13, 2008 2:05 PM To: SunWuKung Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] dynamic crosstab Hi, SunWuKung wrote: > Hi, > > I found this to create dynamic crosstabs (where the resulting columns ... > This could work although for hundreds of columns it looks a bit scary > for me. Well I'd say hundreds of columns are always scary, no matter how you do it :-) ... > I know that most db people don't care much about pivot/crosstab in the > db but imagine this situation: > I am storing questionnaire results on people. Since the questionnaires > are created by users I have no other way than using an EAV model like > personID, questionID, responseValue to store responses. Now this table > gets long 300 question per questionnaire, 3000 people and we have 1m > row. Now whenever I need to download this data in my case 2/3rd of it > would be redundant if I could pivot it first - and in a 20MB csv its > significant (I know its a tradeoff between processing and storage). > Moreover my users can't do anything with this dataformat - they need > to pivot it offline anyway, which is not easy (Excel cant do it, > Access cant do it, numberGo cant do it for different reasons). What about not pivoting it? You can run your analysis directly against your database. > Although the application could do it I think this is a generic > functionality that the database is more suited for. Well after all you want a CSV not a table. You could shortcut this with a generic query which creates array out of your "columns" and join them to a CSV line. This would just be outputted as one single column from database. > Please let me know if you know of a good db based way to create a > dynamic crosstab in Postgres - or why there shouldn't be one. See above :-) Regards Tino Internal Virus Database is out-of-date. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.0/1137 - Release Date: 11/18/2007 5:15 PM
В списке pgsql-general по дате отправления: