Re: custom crosstab question

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