From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SQL Challenge: Arbitrary Cross-tab |
Date: | 2004-08-18 02:55:11 |
Message-ID: | 200408171955.11850.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | elein | 2004-08-18 03:18:36 | Re: SQL Challenge: Arbitrary Cross-tab |
Previous Message | Josh Berkus | 2004-08-18 02:45:15 | Re: multi column foreign key for implicitly unique columns |