Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: SQL Challenge:  Arbitrary Cross-tab
Date: Tue, 17 Aug 2004 19:55:11 -0700
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200408171955.11850.josh@agliodbs.com>

Folks,

I have a wierd business case.  Annoyingly it has to be written in *portable* 
SQL92, which means no arrays or custom aggregates.   I think it may be 
impossible to do in SQL which is why I thought I'd give the people on this 
list a crack at it.   Solver gets a free drink/lunch on me if we ever meet at 
a convention.

The Problem:  for each "case" there are from zero to eight "timekeepers" 
authorized to work on the "case", out of a pool of 150 "timekeepers".  This 
data is stored vertically:

authorized_timekeepers:
case_id 	| timekeeper_id
213447	| 047
132113	| 021
132113	| 115
132113	| 106
etc.

But, a client's e-billing application wants to see these timekeepers displayed 
in the following horizontal format:

case_id	| tk1	| tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8
213447	| 047 |     |     |     |     |     |     |     |
132113      | 021 | 115 | 106 | 034 | 109 | 112 | 087 |
etc.

Order does not matter for timekeepers 1-8.

This is a daunting problem because traditional crosstab solutions do not work; 
timekeepers 1-8 are coming out of a pool of 150.

Can it be done?  Or are we going to build this with a row-by-row procedural 
loop? (to reiterate: I'm not allowed to use a custom aggregate or other 
PostgreSQL "advanced feature")

-- 
Josh Berkus
Aglio Database Solutions
San Francisco