cross tab confusion

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).

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


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.

Thanks for any help.

--
Matt Johnson



__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com