在Server 2008中生成数据轴

Question

我试图在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

Answer 1

看到预期的输出会很有帮助，但据猜测，您需要这样做：

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

...if您希望在每门课程的一列中看到每个学生的"Lv“值的总和。