1

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
3

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.

  • Yes - that is exactly what I was after! Thank you! – IcyPopTarts Jun 14 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.