I am trying to display the results from my database table using the pivot function but it's not displaying them as I need them. The columns in my database are below:
ScoreID, PupilID, Subject, Year, Score
I would like to display a pivot table like this:
ScoreID, PupilID, Subject, Year1, Year2, Year3, Year4
The SQL statement I am using is below:
SELECT * FROM Scores PIVOT(SUM(Score) FOR Year IN ( [Year1],[Year2], [Year3], [Year4] )) AS PVTTable Where (PupilID = 10016)
However, this is producing a separate row for every years score. So I end up with one row for English Year 1 score, another row for English Year 2 score, etc. I only want to display one row for each subject with the scores in the columns on that row.
Thanks in advance for any help you can provide.