Skip site navigation (1) Skip section navigation (2)

Re: custom crosstab question

From: "Michael Swierczek" <mike(dot)swierczek(at)gmail(dot)com>
To: "Joe Conway" <mail(at)joeconway(dot)com>
Cc: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: custom crosstab question
Date: 2007-04-25 17:19:23
Message-ID: 68b5b5880704251019y20a19808i84de3258f810ab28@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)joeconway(dot)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
>

In response to

Responses

pgsql-novice by date

Next:From: Steve CrawfordDate: 2007-04-25 17:24:06
Subject: Re: how to conditionally append
Previous:From: Francois DeliegeDate: 2007-04-25 17:13:35
Subject: Re: moving data from windows to linux

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group