Re: [GENERAL] dynamic crosstab

2008-02-14 Thread Klein Balazs 
I can't imagine how I could store data directly that way (beside the usual
thing that whenever I can I aim to store scalar value in a column).

To do what you suggest I could have this:
 1 (aaa,bbb,ccc)
 2 (ddd,NULL,eee)
but for this I would need to store a NULL for a person for all the questions
he/she didn't answer. Now answers may come from all sorts of questionnaires
so most people will only have responses on a subset, so this does not seem
feasible.

Or this:
 1 (aaa,bbb,ccc)
 2 (ddd,eee)
but this would be loosing the purpose - there is no longer a meaningful way
to compare the same info at different people.

So directly storing the info in this structure does not seem to be the way
for me. On the other hand a query may be able to generate the proper array
without the usual problem of outputting unknown number of columns.

thx
B.

-Original Message-
From: Erik Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 14, 2008 6:14 PM
To: Balázs Klein
Cc: 'Tino Wildenhain'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] dynamic crosstab


On Feb 14, 2008, at 10:56 AM, Balázs Klein wrote:

 -Original Message-
 From: Erik Jones [mailto:[EMAIL PROTECTED]
 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


 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.

My point was to get rid of the the EAV setup.  Something like:

CREATE TABLE questions (
question_id serial primary key,
question text not null
);

CREATE TABLE people (
person_id serial primary key,

);

CREATE TABLE answers (
person_id integer references people,
answers text[]
);

where the indexes into answers are ids from questions.  You don't get  
any easy foreign keys for those indexes into the questions table,  
which you definitely don't have with the EAV setup anyway, but with  
this you don't need any kind of pivot/crosstab functionality.

Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
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 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] dynamic crosstab

2008-02-13 Thread Klein Balazs 
Yes, thanks.
The problem with those function is that they all have an AS (columname type,
...) part or equivalent.

-Original Message-
From: Masse Jacques [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 13, 2008 10:20 AM
To: SunWuKung; pgsql-general@postgresql.org
Subject: RE: [GENERAL] dynamic crosstab


 
 I found this to create dynamic crosstabs (where the resulting 
 columns are not known beforehand): 
 http://www.ledscripts.com/tech/article/view/5.html
 (Thanks for Denis Bitouzé on
 http://www.postgresonline.com/journal/index.php?/archives/14-C
 rossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html
 for pointing it out.).
 This is basically dynamically generating an SQL string with CASE ...
 WHEN that will create a view.
 This could work although for hundreds of columns it looks a 
 bit scary for me.
 Isn't there a more elegant way to achieve this with tablefunc 
 crosstab and if there isn't don't you think it could/should be there?
 There is a syntax where you could specify the columns with a 
 SELECT DISTINCT statement - couldn't it also generate the 
 enumeration string eg. presuming that all returning colums 
 are stored as text?
 Or if that is not possible instead of the enumeration part 
 wouldn't it be better to put a name of the view that could be 
 created/recreated?
 
 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).
 Although the application could do it I think this is a 
 generic functionality that the database is more suited for.
 
 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.
 Thanks and regards.
 SWK

Have you tried this crosstab?

http://www.postgresql.org/docs/8.3/interactive/tablefunc.html


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/