Transposing rows into columns - TSQL Statement

morinia
morinia used Ask the Experts™
on
I have a table that has rows for each member as their respective subject and grade.  Is there a way I can tranpose these in SQL to put the rows into concatenated columns.
(See attachments)
Rows.xlsx
Columns.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer
Commented:
try this.
SELECT ID, 
       LName, 
       FName, 
       'Subject1' AS Subject1, 
       MAX(CASE 
             WHEN Subject = 'Subject1' THEN Grade 
           END)   AS Grade1, 
       'Subject2' AS Subject1, 
       MAX(CASE 
             WHEN Subject = 'Subject2' THEN Grade 
           END)   AS Grade2, 
       'Subject3' AS Subject1, 
       MAX(CASE 
             WHEN Subject = 'Subject3' THEN Grade 
           END)   AS Grade3, 
       'Subject4' AS Subject1, 
       MAX(CASE 
             WHEN Subject = 'Subject4' THEN Grade 
           END)   AS Grade4, 
       'Subject5' AS Subject1, 
       MAX(CASE 
             WHEN Subject = 'Subject5' THEN Grade 
           END)   AS Grade5 
  FROM Table1 
 GROUP BY ID, 
          LName, 
          FName  

Open in new window

Top Expert 2011
Commented:
You can use PIVOT operator. Here is a link that may be helpful
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

another one for dynamic pivot:
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial