Sorting an aggregated column - Mailing list pgsql-sql

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
notnull 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'tsee 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