如何利用T-SQL來做到類似Excel的樞紐分析表

假設我有三個資料表分別是Students、Subjects及Scores，用來存放學生資料、科目資料及考試成績資料（如下圖），若考試成績未達60分，則必須參加補考，考到及格為止。

因此我們可能以下列T-SQL來取得每個學生每一科的最高分數：

   1:  select t2.Name as StudentName,t3.Name as SubjectName

   2:              ,MAX(t1.Score) as MaxScore

   3:  from dbo.Scores t1

   4:  inner join dbo.Students t2 

   5:      on t1.StudentID = t2.StudentID

   6:  inner join dbo.Subjects t3

   7:      on t1.SubjectID = t3.SubjectID

   8:  group by t2.Name,t3.Name

   9:  order by t2.Name,t3.Name

執行結果如下：

若我們想把每個學生的每一科目變成是資料行，如同EXCEL的樞紐分析表一樣（如下圖），在SQL Server 2005以後的版本，可以利用Pivot關係運算子來完成。

完整程式碼如下：

   1:  select p1.StudentName as 姓名,p1.國文,p1.英文,p1.數學,p1.物理

   2:  from 

   3:  (

   4:      select t2.Name as StudentName,t3.Name as SubjectName,t1.Score

   5:      from dbo.Scores t1

   6:      inner join dbo.Students t2 

   7:          on t1.StudentID = t2.StudentID

   8:      inner join dbo.Subjects t3

   9:          on t1.SubjectID = t3.SubjectID

  10:  ) as s1

  11:  pivot 

  12:  (

  13:      MAX(s1.Score)

  14:      for s1.SubjectName

  15:      IN ([國文],[英文],[數學],[物理])

  16:  ) as p1    

執行結果如下：

原始文章出自於此