Re: cross tab confusion

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(dot)Elphick(at)lfix(dot)co(dot)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