Message-ID: <3257.192.168.1.16.1042486225.squirrel@mainbox.archonet.com>
In-Reply-To: <web-2312151@davinci.ethosmedia.com>
References: <1493.192.168.1.16.1042462021.squirrel@mainbox.archonet.com>
	<web-2312151@davinci.ethosmedia.com>
Date: Mon, 13 Jan 2003 19:30:25 -0000 (GMT)
Subject: Re: Crosstab-style query in pure SQL
From: dev@archonet.com
To: "Josh Berkus" <josh@agliodbs.com>
Cc: dev@archonet.com, pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Importance: Normal

> Richard,
>
>> 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".
>
> Ah!   A classic SQL problem.
>
> Take a look at Joe Celko's "SQL for Smarties": he does a good job of
> defining and discussing the three different solutions to the "Crosstab
> Query" problem.
>
> A second method you can use is the subselect method:
>
> SELECT  proj_id, sortorder, qart1.q1, quart2.q2 ....
> FROM milestones
> LEFT OUTER JOIN ( SELECT proj_id, description as q1
>            FROM milestones WHERE qtr = 1) quart1
>          ON quart1.proj_id = milestones.proj_id
> LEFT OUTER JOIN ( SELECT proj_id, description q2 ...
>
> However, the above is generally chosen over the CASE statement method
> when the crosstab involves multiple tables; in your case, it is not a
> performance or similicity gain.

Thanks Josh, but that still leaves me with nulls if I join on sortorder
too, and duplicates if not (as you imply, since it's equivalent to the
CASE option). The problem is that since I defined "sortorder" as a serial,
equivalent rows of a specific project don't match across the key.

Without calculating a "row_index" based on (proj_id,sortorder) it doesn't
look like there's anything to be done without procedural help. Didn't
think there was anything simple - my fault for not having common keys to
match up output rows - oversimplified the input stage and I'm paying for
it on output.

- Richard Huxton