在 SQL Server 2008 中生成數據透視表

我正在嘗試在 SQL Server 2008 中創建數據透視表，但出現錯誤

關鍵字“FOR”附近的語法不正確

下面是範例 DDL 和我嘗試執行的查詢 - 應如何更改此查詢以使其成功執行？

     Declare @House Table
 (
   studID int
   ,sID int
   ,course varchar(500)
   ,cls int
   ,lv decimal(10,2)
 )

 Insert Into @House (studID, sID, course, cls, lv) VALUES
 (52, 52, 'Remaining Metrics (14th Century)', 31, '12.5')
 ,(52, 52, 'History Of The World (Part I)', 33, '200.0')
 ,(52, 52, 'Singing Socks In The Rain (Part III)', 12, '3.5')
 ,(11, 11, 'Remaining Metrics (14th Century)', 31, '2.5')
 ,(11, 11, 'History Of The World (Part I)', 33, '1.5')
 ,(11, 11, 'Singing Socks In The Rain (Part III)', 12, '2.4')


 Select studID from @House
 PIVOT (lv FOR course IN ([Remaining Metrics (14th Century)],[History Of The World (Part I)], [Singing Socks In The Rain (Part III)])) As Drafts

查看您的預期輸出會很有幫助，但猜測一下，您需要這個：

SELECT
   studID,
   [Remaining Metrics (14th Century)],
   [History Of The World (Part I)],
   [Singing Socks In The Rain (Part III)]
FROM
   (SELECT StudID, Course, SUM(lv) AS Lv FROM @House GROUP BY StudID, Course) AS X
PIVOT
   (
   SUM(lv) FOR course IN ([Remaining Metrics (14th Century)], [History Of The World (Part I)], [Singing Socks In The Rain (Part III)])
   ) As Drafts

…如果您想在每門課程的列中查看每個學生的“Lv”值的總和。

引用自：https://dba.stackexchange.com/questions/176320