SQL Server 2008 R2 – Generating Pivot

pivotsql serversql-server-2008-r2t-sql

I am attempting to create a Pivot Table in SQL Server 2008, but I am getting an error of

Incorrect syntax near the keyword 'FOR'

Below is sample DDL and the query I tried to execute – how should this query be changed so that it will execute succesfully?

      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

Best Answer

It would be helpful to see your expected output, but at a guess, you need this:

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 you want to see the total of each students' "Lv" values, in a column for each course.

Related Question