Re: custom crosstab question

[Joe Conway <mail(at)joeconway(dot)com>]

Michael Swierczek wrote:
> On 4/25/07, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
> > Joe,
> >      That's exactly what I needed.   I completely missed that it would
> > be possible with the tablefunc/crosstab.   Since you're the main
> > (only?) name I see associated with that code, I'm sure you would know.
> >
> > -Mike
>
> I spoke too soon, there's an additional factor at play that
> complicates things.
> Most of the questions take a single answer, but the multiple select
> questions can have several answers.   So for a regular question with
> code 'drug', a given survey_event can have 0 or 1 entries in the
> answer table with question_code 'drug'.  For a multiple select
> question like, 'health', 0-7 entries are possible in the answer table,
> and we want them exported as columns 'health0', 'health1', 'health2'
> through 'health7' and each column populated according to whether that
> section of the multiple select was chosen.

It seems to me that if you really want health0 and health1 tracked 
independently, they should each be considered separate questions. Then 
the existing code would "just work". Perhaps if it is important to tie 
those "healthN" questions together, you could have some other attribute 
for questions that allows grouping. So, for example, grp_id = 0 means 
this question is "stand alone" and grp_id > 0 is a grouped question. 
Something like:

create table survey_question_grps (
 grp_id int,
 grp_name text
);

insert into survey_question_grps values
(0, 'stand alone question'),
(1, 'health');

create table survey_question (
 id int,
 survey_id int,
 question_code text,
 grp_id int,
 question_order int
);

insert into survey_question values
 (1,1,'drug',0,1),
 (2,1,'marijuana',0,2),
 (3,1,'sick',0,3),
 (4,1,'health1',1,4),
 (5,1,'health2',1,5);

Joe