Message-ID: <4122E60F.2080403@joeconway.com>
Date: Tue, 17 Aug 2004 22:15:59 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
Cc: 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:
> 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")
> 

This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed 
version; crosstab(sourcesql, ncols)) works. If you really need it to be 
portable, though, application layer procedural code is likely to be the 
easiest and fastest way to go. crosstab just wraps the procedural code 
in an SRF for you.

Joe