如何使用数据透视表

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:

我试图使用pivot函数显示我的数据库表中的结果,但它不显示它们,因为我需要它们。我的数据库中的列如下:

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:

我正在使用的SQL语句如下:

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.

提前感谢您提供的任何帮助。

1 个解决方案

#1

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

有关详细信息,请参阅: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