none
Problem with PIVOT

    Question

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

    Sunday, February 04, 2007 11:54 AM

Answers

  • 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
    Sunday, February 04, 2007 10:09 PM