From: Joe Conway <mail@joeconway.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Challenge: Arbitrary Cross-tab
Date: Tue, 17 Aug 2004 22:15:59 -0700
Message-ID: <4122E60F.2080403@joeconway.com> (raw)
In-Reply-To: <200408171955.11850.josh@agliodbs.com>
References: <200408171955.11850.josh@agliodbs.com>
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
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: mail@joeconway.com, josh@agliodbs.com
Subject: Re: SQL Challenge: Arbitrary Cross-tab
In-Reply-To: <4122E60F.2080403@joeconway.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