Re: Pulling my hair out with this query

[Aziz]

It's very easy if you have MySQL 4.1 which permits the use of sub-queries, like so:

"SELECT DISTINCT NAME,
(SELECT TEST1 FROM mydata AS m2 WHERE m2.YEAR = 2002 AND m2.NAME = m1.NAME) AS '2002',
(SELECT TEST1 FROM mydata AS m2 WHERE m2.YEAR = 2003 AND m2.NAME = m1.NAME) AS '2003',
(SELECT TEST1 FROM mydata AS m2 WHERE m2.YEAR = 2004 AND m2.NAME = m1.NAME) AS '2004',
FROM mydata AS m1 ORDER BY NAME"

I hope it works.

Regards,
Aziz