Date: Tue, 17 Aug 2004 20:35:53 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge:  Arbitrary Cross-tab
In-Reply-To: <200408171955.11850.josh@agliodbs.com>
Message-ID: <20040817202141.W84557@megazone.bigpanda.com>
References: <200408171955.11850.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.