Message-ID: <41237FC2.8090404@travelamericas.com>
Date: Wed, 18 Aug 2004 09:11:46 -0700
From: Chris Travers <chris@travelamericas.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7) Gecko/20040616
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: SQL Challenge:  Arbitrary Cross-tab
References: <200408171955.11850.josh@agliodbs.com>
In-Reply-To: <200408171955.11850.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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