From: dev@archonet.com
To: Josh Berkus <josh@agliodbs.com>
Cc: dev@archonet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Crosstab-style query in pure SQL
Date: Mon, 13 Jan 2003 19:30:25 -0000 (GMT)
Message-ID: <3257.192.168.1.16.1042486225.squirrel@mainbox.archonet.com> (raw)
In-Reply-To: <web-2312151@davinci.ethosmedia.com>
References: <1493.192.168.1.16.1042462021.squirrel@mainbox.archonet.com>
<web-2312151@davinci.ethosmedia.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, josh@agliodbs.com
Subject: Re: Crosstab-style query in pure SQL
In-Reply-To: <3257.192.168.1.16.1042486225.squirrel@mainbox.archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox