Josh Berkus wrote:
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.
Might be possible. Would certainly be ugly.
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")
If it can be done, it might be extremely ugly. I am thinking a massive set of left self joins (since there could be between 0 and 8).
Something like:
select case_id FROM authorized_timekeeper t0
LEFT JOIN
(SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper
GROUP BY case_id) t1
ON case_id
LEFT JOIN
(SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper
WHERE timekeeper_id <> t1.timekeeper
GROUP BY case_id) t2
ON case_id
LEFT JOIN
(SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper
WHERE timekeeper_id NOT IN (t1.timekeeper, t2.timekeeper)
GROUP BY case_id) t3
etc....If this is not an option, instead I would create a series of views.
Something like:
CREATE VIEW t1 AS select case_id, min(timekeeper_id) AS tk_id
from authorized_timekeepers
group by case_id;
CREATE VIEW t2 AS select case_id, min(timekeeper_id) AS tk_id
from authorized_timekeepers
WHERE tk_id NOT IN (SELECT tk_id FROM t1)
group by case_id;
CREATE VIEW t3 AS select case_id, min(timekeeper_id) AS tk_id
FROM authorized_timekeepers
WHERE tk_id NOT IN (SELECT tk_id FROM t1)
AND tk_id NOT IN (SELECT tk_id FROM t2)
GROUP BY case_id;
Etc.
Then you do a left join among the views.Hope that this helps.
Best Wishes, Chris Travers
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
