X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C3C3BD1DC2D for ; Tue, 23 Mar 2004 03:26:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95178-04 for ; Mon, 22 Mar 2004 23:26:35 -0400 (AST) Received: from ozimelb03.ozicom.com (unknown [203.185.247.97]) by svr1.postgresql.org (Postfix) with ESMTP id 8B418D1D64C for ; Mon, 22 Mar 2004 23:26:20 -0400 (AST) content-class: urn:content-classes:message Subject: Sorting an aggregated column MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Date: Tue, 23 Mar 2004 14:26:15 +1100 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Sorting an aggregated column Thread-Index: AcQQhpkzwM2I/2buQEqcqkW6KH/VFA== From: "David Witham" To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/227 X-Sequence-Number: 17326 Hi all, I ran these commands: create temp table dw_survey(survey int,question int,answer_bool boolean,ans= wer_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=20 -------------+-------------------+----------- survey | integer |=20 question | integer |=20 answer_bool | boolean |=20 answer_int | integer |=20 answer_char | character varying |=20 survey | question | answer_bool | answer_int | answer_char=20 --------+----------+-------------+------------+------------- 1 | 1 | t | |=20 1 | 2 | f | |=20 1 | 3 | t | |=20 1 | 4 | | 123 |=20 1 | 5 | | 21 |=20 1 | 6 | | 1 |=20 2 | 1 | t | |=20 2 | 2 | t | |=20 2 | 3 | t | |=20 2 | 4 | | 3 |=20 2 | 5 | | 2 |=20 2 | 6 | | 1 |=20 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" co= lumn. I can do the second case with a user-defined string concatenating aggregate: select survey, list ( case when answer_bool =3D 't' then 'y'::varchar when answer_bool =3D '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=20=20=20=20=20=20=20=20=20 --------+--------------------- 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 answ= ers will come out in "question" order. I can't see how to incorporate sorti= ng 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