Re: SQL Challenge: Arbitrary Cross-tab

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