Re: custom crosstab question - Mailing list pgsql-novice

On 4/25/07, Michael Swierczek <mike.swierczek@gmail.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.

However, I fear I am crossing from "I have a novice question" into
"This is complicated enough that requesting help is exploiting the
generosity of the community for free consulting services."  I won't
pursue it any further here.

-Mike




> On 4/25/07, Joe Conway <mail@joeconway.com> wrote:
> > Michael Swierczek wrote:
> > > Ladies and Gentlemen,
> > > This will be long, I apologize.   I'm mostly looking for someone to
> > > tell me there's an obvious solution I'm missing.
> > >
> >
> > Try something like this:
> >
> > create table survey_question (id int, survey_id int, question_code text,
> > question_order int);
> >
> > insert into survey_question values
> > (1,1,'drug',1),(2,1,'marijuana',2),(3,1,'sick',3);
> >
> > create table answer (id int, survey_event_id int, question_code text,
> > answer_order int, answer_value int);
> >
> > insert into answer values (1,1,'drug',1,1),(2,1,'sick',2,1);
> >
> > select * from crosstab(
> >    'select survey_event_id, question_code, answer_value from answer',
> >    'select question_code from survey_question order by question_order'
> > ) as (survey_event_id int, cat1 int, cat2 int, cat3 int);
> >   survey_event_id | cat1 | cat2 | cat3
> > -----------------+------+------+------
> >                 1 |    1 |      |    1
> > (1 row)
> >
> > In 8.2 you could do "select question_code from survey_question order by
> > question_order" first, and in your application build this query
> > dynamically and run it:
> >
> > select * from crosstab(
> >    'select survey_event_id, question_code, answer_value from answer',
> >    'values (''drug''), (''marijuana''), (''sick'')'
> > ) as (survey_event_id int, drug int, marijuana int, sick int);
> >   survey_event_id | drug | marijuana | sick
> > -----------------+------+-----------+------
> >                 1 |    1 |           |    1
> > (1 row)
> >
> > HTH,
> >
> > Joe
> >
>