Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E21B5476033
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 07:47:01 -0500 (EST)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by postgresql.org (Postfix) with ESMTP id 4BF29475E1F
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 07:47:01 -0500 (EST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2)
	id 18Y3zb-0005bG-0Z
	for pgsql-sql@postgresql.org; Mon, 13 Jan 2003 12:47:03 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP id 66FC915A6D
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 12:47:02 +0000 (GMT)
Received: from archonet.com (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with SMTP id D686B1593D
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 12:47:01 +0000 (GMT)
Received: from 192.168.1.16 (SquirrelMail authenticated user dev)
	by mainbox.archonet.com with HTTP;
	Mon, 13 Jan 2003 12:47:01 -0000 (GMT)
Message-ID: <1493.192.168.1.16.1042462021.squirrel@mainbox.archonet.com>
Date: Mon, 13 Jan 2003 12:47:01 -0000 (GMT)
Subject: Crosstab-style query in pure SQL
From: dev@archonet.com
To: pgsql-sql@postgresql.org
X-Mailer: SquirrelMail (version 1.3.2)
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
X-Priority: 3
Importance: Normal
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/168
X-Sequence-Number: 11033

TIA all

I have a table containing milestones achieved for projects, marked as
being in particular quarters.

CREATE TABLE milestones (
    proj_id     int4,
    sortorder   SERIAL,
    qtr         int4,
    description varchar(200)
);

Now I need the milestone descriptions output across the page like:
  proj_id | q1 | q2 | q3 | q4
and sorted according to "sortorder".

Judicious use of CASE can get me the quarterly columns but I need to
eliminate holes. Any clever ideas without resorting to procedural
solutions (either table function or application code)?

TIA

- Richard Huxton