CODEWITHSUNDEEP
sqlsql-serverpivot
user1711657
user1711657

Reputation: 83

How to use use pivot table

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.

Upvotes: 0

Views: 65

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

Try this:

SELECT PupilID, Subject, [Year1], [Year2], [Year3], [Year4]
FROM (
    SELECT PupilID, Subject, Year, Score
    FROM Scores
    --Where (PupilID = 10016) 
    ) AS DT
PIVOT(SUM(Score) FOR Year IN ([Year1],[Year2], [Year3], [Year4])) AS PVT

For further information, please see:
https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query

Upvotes: 1

Related Questions