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: Wed, 18 Aug 2004 10:54:39 -0700
Message-ID: <412397DF.4050208@joeconway.com> (raw)
In-Reply-To: <200408180956.14356.josh@agliodbs.com>
References: <200408171955.11850.josh@agliodbs.com>
<4122E60F.2080403@joeconway.com>
<200408180956.14356.josh@agliodbs.com>
Josh Berkus wrote:
>>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.
>
> No, you're missing one factor in the spec. Timekeeper_1 for case_id = 182738
> is not the same timekeeper as Timekeeper_1 for case_id = 217437. That's why
> traditional crosstab plans don't work.
No, I understood. E.g.
create table authorized_timekeepers (
case_id int,
timekeeper_id text
);
insert into authorized_timekeepers values(213447,'047');
insert into authorized_timekeepers values(132113,'021');
insert into authorized_timekeepers values(132113,'115');
insert into authorized_timekeepers values(132113,'106');
insert into authorized_timekeepers values(140000,'106');
insert into authorized_timekeepers values(140000,'021');
insert into authorized_timekeepers values(140000,'115');
insert into authorized_timekeepers values(140000,'108');
insert into authorized_timekeepers values(140000,'006');
insert into authorized_timekeepers values(140000,'042');
insert into authorized_timekeepers values(140000,'142');
insert into authorized_timekeepers values(140000,'064');
insert into authorized_timekeepers values(140000,'999');
select * from crosstab('select case_id, ''cat'' as cat, timekeeper_id
from authorized_timekeepers order by 1',8)
as t(case_id int, tk1 text, tk2 text, tk3 text, tk4 text, tk5 text, tk6
text, tk7 text, tk8 text);
case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8
---------+-----+-----+-----+-----+-----+-----+-----+-----
132113 | 021 | 115 | 106 | | | | |
140000 | 106 | 021 | 115 | 108 | 006 | 042 | 142 | 064
213447 | 047 | | | | | | |
(3 rows)
Or even:
select * from crosstab('select case_id, ''cat'' as cat, timekeeper_id
from authorized_timekeepers order by 1',4)
as t(case_id int, tk1 text, tk2 text, tk3 text, tk4 text);
case_id | tk1 | tk2 | tk3 | tk4
---------+-----+-----+-----+-----
132113 | 021 | 115 | 106 |
140000 | 106 | 021 | 115 | 108
213447 | 047 | | |
(3 rows)
But I know that doesn't help you with portability.
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: <412397DF.4050208@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