Re: custom crosstab question

["Michael Swierczek" <mike(dot)swierczek(at)gmail(dot)com>]

On 4/26/07, Joe Conway <mail(at)joeconway(dot)com> wrote:
> 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:

The software has been in production for a few years, and although I
have the ability and authority to retrofit the existing data, I'm
nervous about doing so.   A number of seemingly innocent tweaks in the
past caused major headaches further along.

But it's definitely worth considering.
-Mike


> 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