X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1420C5E46E2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Aug 2004 14:54:38 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83552-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 18 Aug 2004 17:54:43 +0000 (GMT)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id C46745E40BB
	for <pgsql-sql@postgresql.org>; Wed, 18 Aug 2004 14:54:34 -0300 (ADT)
Received: from [206.19.64.3] (account jconway HELO [192.168.56.95])
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 2129667; Wed, 18 Aug 2004 10:47:05 -0700
Message-ID: <412397DF.4050208@joeconway.com>
Date: Wed, 18 Aug 2004 10:54:39 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803
X-Accept-Language: en-us, en
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>
	<4122E60F.2080403@joeconway.com>
	<200408180956.14356.josh@agliodbs.com>
In-Reply-To: <200408180956.14356.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200408/209
X-Sequence-Number: 18706

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