Problem with PIVOT

I am currently trying to write a query that I can use in C# to produce a score table for MotorCycle races - as I know that using the PIVOT command you have to specify the column names.

I have 3 seperate tables - which I join to produce a table which I then try to rearrange with PIVOT as below.

SELECT Name,RaceName,Position FROM

(SELECT dbo.Rider.Name, dbo.Race.RaceName, dbo.Position.Position FROM

dbo.Rider INNER JOIN dbo.Position ON dbo.Rider.ID = dbo.Position.Rider_ID INNER JOIN dbo.Race ON dbo.Race.ID = dbo.Position.Race_ID) AS A

PIVOT(A.Name FOR A.RaceName IN ([Brands Hatch],[Silverstone])) AS P

When I try to run the query it says I have an error near FOR. I have taken the syntax from msdn and other sites.

Hopefully someone can help!!!!

Thanks!

PIVOT requires an aggregate function to be in there before the 'FOR' bit. So if you had MAX(A.Name), then you might get what you want.

Rob