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
