Date: Tue, 17 Aug 2004 20:18:36 -0700
From: elein <elein@varlena.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge:  Arbitrary Cross-tab
Message-ID: <20040817201836.D32286@cookie.varlena.com>
Mail-Followup-To: Josh Berkus <josh@agliodbs.com>,
	pgsql-sql@postgresql.org
References: <200408171955.11850.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200408171955.11850.josh@agliodbs.com>
User-Agent: Mutt/1.3.22.1i

I would use my report writer, but in any case you'd
want at least 2 separate queries, maybe three to
keep it simple and readable.

If you are allowed to use stored procedures you can
build up the output by using simple concats instead
of text aggregation (which is a procedure of simple
concats).  

Using loops and subqueries you should be to construct
the heading (count distinct timekeeper_id) and then select
the data row by row concatenating results before you
send it out.

This is a non-solution which effectively hides the
aggregation in a function.

Or write it in a client perl app if you must.

You can't really do it w/o loops or aggregates.
(I wish (hope?) I were wrong about this.)

--elein


On Tue, Aug 17, 2004 at 07:55:11PM -0700, 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.
> 
> 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")
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)