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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
