Hi all,
I ran these commands:
create temp table dw_survey(survey int,question int,answer_bool boolean,answer_int
int,answer_char varchar);
insert into dw_survey values(1,1,'t',null,null);
insert into dw_survey values(1,2,'f',null,null);
insert into dw_survey values(1,3,'t',null,null);
insert into dw_survey values(1,4,null,123,null);
insert into dw_survey values(1,5,null,21,null);
insert into dw_survey values(1,6,null,1,null);
insert into dw_survey values(2,1,'t',null,null);
insert into dw_survey values(2,2,'t',null,null);
insert into dw_survey values(2,3,'t',null,null);
insert into dw_survey values(2,4,null,3,null);
insert into dw_survey values(2,5,null,2,null);
insert into dw_survey values(2,6,null,1,null);
and I now have a table with data like this:
Table "pg_temp_5.dw_survey"
Column | Type | Modifiers
-------------+-------------------+-----------
survey | integer |
question | integer |
answer_bool | boolean |
answer_int | integer |
answer_char | character varying |
survey | question | answer_bool | answer_int | answer_char
--------+----------+-------------+------------+-------------
1 | 1 | t | |
1 | 2 | f | |
1 | 3 | t | |
1 | 4 | | 123 |
1 | 5 | | 21 |
1 | 6 | | 1 |
2 | 1 | t | |
2 | 2 | t | |
2 | 3 | t | |
2 | 4 | | 3 |
2 | 5 | | 2 |
2 | 6 | | 1 |
Answers to a survey can be one of three types - boolean, integer or varchar.
There can be any number of questions in a survey.
I want to summarise the results of the survey like this:
survey | answer1 | answer2 | answer3 | answer4 | answer5 | answer6
-------+---------+---------+---------+---------+---------+--------
1 | t | f | t | 123 | 21 | 1
2 | t | t | t | 3 | 2 | 1
Or even like this:
survey | answers
-------+---------------
1 | t,f,t,123,21,1
2 | t,t,t,3,2,1
In both cases the order of the answers must be ordered by the "question" column.
I can do the second case with a user-defined string concatenating aggregate:
select survey, list (
case when answer_bool = 't' then 'y'::varchar
when answer_bool = 'f' then 'n'::varchar
when answer_int is not null then answer_int::varchar
when answer_char is not null then answer_char::varchar
end
)
from dw_survey
group by survey
order by survey;
survey | list
--------+---------------------
1 | y, n, y, 123, 21, 1
2 | y, y, y, 3, 2, 1
This output is correct in this case but there is no guarantee that the answers will
come out in "question" order. I can't see how to incorporate sorting by the "question"
column using this approach.
Can anyone suggest either how to improve my current approach or a different approach
to get the desired result?
Thanks,
David Witham
Telephony Platforms Architect
Unidial Pty Ltd
Level 1, 174 Peel St North Melbourne,VIC 3051
Australia
Ph: 03 8628 3383
Fax: 03 8628 3399
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
