From: elein <elein@varlena.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Arbitrary Cross-tab
Date: Tue, 17 Aug 2004 20:18:36 -0700
Message-ID: <20040817201836.D32286@cookie.varlena.com> (raw)
In-Reply-To: <200408171955.11850.josh@agliodbs.com>
References: <200408171955.11850.josh@agliodbs.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: elein@varlena.com, josh@agliodbs.com
Subject: Re: SQL Challenge: Arbitrary Cross-tab
In-Reply-To: <20040817201836.D32286@cookie.varlena.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox