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

Re: custom crosstab question

From: Joe Conway <mail(at)joeconway(dot)com>
To: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: custom crosstab question
Date: 2007-04-25 15:39:24
Message-ID: 462F762C.8060900@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-novice
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: Gary WarnerDate: 2007-04-25 15:50:09
Subject: International Date formats
Previous:From: Francois DeliegeDate: 2007-04-25 15:36:37
Subject: Re: moving data from windows to linux

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