Re: custom crosstab question

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

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
>