On Tue, 17 Aug 2004, Josh Berkus wrote: > 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
If you know it's max 8, I think it may be possible, but I can't think of a way that'd be better than just writing code yourself. Just maybe something like the following would give you three timekeepers: select foo.case_id, foo.v1, foo.v2, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id and timekeeper_id > foo.v2) as v3 from ( select foo.case_id, foo.v1, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id and timekeeper_id > foo.v1) as v2 from (select foo.case_id, foo.v1 from (select foo.case_id, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id) as v1 from (select distinct case_id from authorized_timekeepers) foo ) foo) foo) foo; If that works for 3 (and I think that's standard behavior), then you should be able to extend it to any fixed number using the pattern. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]