Re: cross tab confusion - Mailing list pgsql-general

Matt Johnson wrote:
> Please point out any stark errors in my thinking. This
> won't be a huge app (but then these things grow I
> guess so it's best to get these things right).
>

There is no way to completely automate this just using SQL, but I think
you're on track with using an external language like python to automate
the writing of your query.

As an alternative, you could try the contrib/tablefunc function
crosstab. I've posted a new version that includes an improved crosstab
function (see http://www.joeconway.com/ and look for the link
"contrib/tablefunc with hashed crosstab"). Here's what the solution
might look like for a 20 week semester (the example is long, but only
the last sql statement needs to be run each week):

create table pupil(pupil_id integer, name text);
insert into pupil values(1,'jeff');
insert into pupil values(2,'fred');
insert into pupil values(3,'rita');

create table reading_level(record_id integer, pupil_id integer, week
integer, level float8);
insert into reading_level values(1,1,1,6.1);
insert into reading_level values(2,2,1,4.3);
insert into reading_level values(3,1,2,6.2);
insert into reading_level values(4,2,2,4.4);

create or replace function weeks() returns setof integer as'
declare
   weeknum integer;
begin
   for weeknum in 1..20 loop
     return next weeknum;
   end loop;
   return;
end
' language 'plpgsql';

CREATE TYPE weekly_report_tup AS (
   name text,
   w1 float8,
   w2 float8,
   w3 float8,
   w4 float8,
   w5 float8,
   w6 float8,
   w7 float8,
   w8 float8,
   w9 float8,
   w10 float8,
   w11 float8,
   w12 float8,
   w13 float8,
   w14 float8,
   w15 float8,
   w16 float8,
   w17 float8,
   w18 float8,
   w19 float8,
   w20 float8
);

CREATE OR REPLACE FUNCTION weekly_report(text,text)
RETURNS setof weekly_report_tup
AS '$libdir/tablefunc','crosstab_hash'
LANGUAGE 'C' STABLE STRICT;

select * from weekly_report(
   'select p.name, r.week, r.level from pupil p, reading_level r
    where r.pupil_id = p.pupil_id',
   'select * from weeks()'
);
  name | w1  | w2  | w3 | w4 | w5 | w6 | w7 | w8 | w9 | w10 | w11 | w12 |
------+-----+-----+----+----+----+----+----+----+----+-----+-----+-----+
  jeff | 6.1 | 6.2 |    |    |    |    |    |    |    |     |     |
|
  fred | 4.3 | 4.4 |    |    |    |    |    |    |    |     |     |     |
(2 rows)

I've trimmed the output at w12 for clarity in the email, but 13-20 would
be there as well. You could easily extend this to 26 or 52 weeks if you
wanted. Now as more data is added...

insert into reading_level values(5,1,3,6.3);
insert into reading_level values(6,2,3,4.5);
insert into reading_level values(7,3,3,7.2);
select * from weekly_report(
   'select p.name, r.week, r.level from pupil p, reading_level r
    where r.pupil_id = p.pupil_id',
   'select * from weeks()'
);
  name | w1  | w2  | w3  | w4 | w5 | w6 | w7 | w8 | w9 | w10 | w11 | w12
------+-----+-----+-----+----+----+----+----+----+----+-----+-----+-----
  jeff | 6.1 | 6.2 | 6.3 |    |    |    |    |    |    |     |     |
  fred | 4.3 | 4.4 | 4.5 |    |    |    |    |    |    |     |     |
  rita |     |     | 7.2 |    |    |    |    |    |    |     |     |
(3 rows)

...you just run the query.

HTH,

Joe