Re: cross tab confusion - Mailing list pgsql-general

On Sat, 2003-03-08 at 19:51, Matt Johnson wrote:
> Folks,
>
> Newbie... So far, I've been building a database with
> MySQL and I'm finding it seems to have a few
> limitations. (I should say it's probably my lack of
> knowledge rather than a failing of MySQl).

Many have found limitations in MySQL.  I have taken over a project from
someone who didn't discover them in time...

> I'm now looking at switching to Postgresql simply
> because it seems more feature-rich, specifically in
> the problem I'm faced with.
>
> I'm building a pupil assessment record. I have these
> two tables:
>
> table `pupil`
>
> pupil_id   name
> 1          jeff
> 2          fred
> 3          rita
>
>
> table `reading_level`
>
> record_id   pupil_id   week   level
> 1           1          1      6.1
> 2           2          1      4.3
> 3           1          2      6.2
> 4           2          2      4.4


Does that record_id column do anything except give you a unique key?
Unless it relates to something in the real world, it is redundant,
because (pupil_id, week) is the obvious primary key -- assuming you have
a new table for each academic year...

> I'd really need to present this data thus:
>
> name   week1   week2   week3
> jeff   6.1     6.2
> fred   4.3     4.4
>
> I believe it will involve a cross tab query, but I'm
> completely lost. The real glory seems to be in writing
> a query that will 'automatically' add new weeks as new
> columns as more data is added to table
> `reading_level`. In other words, the query doesn't
> need rewriting after adding records for week 3, 4 etc.
> Some query cleverness gets the weeks for itself.

You could create a view like this:

CREATE OR REPLACE VIEW pupil_reading_levels
  (name, week1, week2, week3, week4) AS
   SELECT p.name,
          (SELECT r1.level
             FROM pupil AS p1, reading_level AS r1
            WHERE p1.pupil_id = p.pupil_id AND
                  p1.pupil_id = r1.pupil_id AND
                  r1.week = 1) AS week1,
          (SELECT r2.level
             FROM pupil AS p2, reading_level AS r2
            WHERE p2.pupil_id = p.pupil_id AND
                  p2.pupil_id = r2.pupil_id AND
                  r2.week = 2) AS week2,
          (SELECT r3.level
             FROM pupil AS p3, reading_level AS r3
            WHERE p3.pupil_id = p.pupil_id AND
                  r3.week = 3) AS week3
          (SELECT r4.level
             FROM pupil AS p4, reading_level AS r4
            WHERE p4.pupil_id = p.pupil_id AND
                  r4.week = 4) AS week4
     FROM pupil AS p;

This will need all columns defined in advance, which might be
cumbersome, and will certainly give a lot of empty columns early in the
year.  I can't think of a way to define a view to create columns
dynamically

Alternatively, use this query:

 SELECT p.name, r.week, r.level
   FROM pupil AS p, reading_level AS r
  WHERE p.pupil_id = r.pupil_id
  ORDER BY p.name, r.week;

to put out data into a Perl script.  The script would construct and
display the report, either directly or by loading a temporary table and
running queries on that..

Perl::DBI would be an appropriate tool to use to handle the SQL
connection and queries within your Perl program.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And God is able to make all grace abound to you, so
      that in all things at all times, having all that you
      need, you will abound in every good work."
                                  II Corinthians 9:8