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!!!!


    Sunday, February 04, 2007 11:54 AM


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

    Sunday, February 04, 2007 10:09 PM