SQL SERVER 2008 QUERY to convert rows to multiple columns

Question

I have attached a screen shot. I have mentioned both input and required output. I need a SQL server 2008/2012 Query, to get the output.

Answer 1

You need to use PIVOT. It should be something like following. If you don't know how PIVOT works, try playing around Excel Pivot with tutorials online and get yourself more familiar with its logic first.

WITH PivotData AS
(
    SELECT
        AssignmentName,
        StudentName,
        Grade
    FROM TableName
)

SELECT
    StudentName,
    Assignment1,
    Assignment2,
    Assignment3
FROM PivotData
PIVOT
(
    SUM(Grade)
    FOR AssignmentName
    IN (Assignment1, Assignment2, Assignment3)
) AS PivotResult
ORDER BY StudentName

PIVOT and UNPIVOT in T-SQL

Pivot in Excel

Answer 2

You can use dynamic sql query.

Query

declare @sql as varchar(max);

select @sql = 'select ' + stuff((
           select ', max(case StudentID when ' 
           + cast(t.StudentID as varchar(10))                               
           + ' then  StudentKey end) as StudentID' 
           + cast(t.StudentID as varchar(10))
           +', max(case StudentID when ' + cast(t.StudentID as varchar(10)) 
           + ' then  StudentName end) as StudentName' 
           + cast(t.StudentID as varchar(10)) 
           from (select distinct top 3 * from studentTable order by StudentID)t
           for xml path('')
        ), 1, 2, '') + ' from studentTable;';

exec(@sql);

And this will give the result in the coulmn order of 1 StudentId then StudentName and so on. Some thing like below.

Result

+------------+--------------+------------+--------------+------------+--------------+
| StudentID1 | StudentName1 | StudentID2 | StudentName2 | StudentID3 | StudentName3 |
+------------+--------------+------------+--------------+------------+--------------+
| 125        |     A        |  225       |     B        |  325       |      C       |
+------------+--------------+------------+--------------+------------+--------------+

And if you want the result like all the studentId column first then the studentName column. Then

Query

declare @sql as varchar(max);

select @sql = 'select ' + stuff((
                    select ', max(case StudentID when ' 
                    + cast(t.StudentID as varchar(10)) 
                    + ' then  StudentKey end) as StudentID' 
                    + cast(t.StudentID as varchar(10))
                    from (select distinct top 3 * from studentTable order by StudentID)t
                    for xml path('')
                    ), 1, 2, '')
                    + ',' 
                    + stuff((
                    select ', max(case StudentID when ' 
                    + cast(t.StudentID as varchar(10)) 
                    + ' then  StudentName end) as StudentName' 
                    +  cast(t.StudentID as varchar(10)) 
                    from (select distinct top 3 * from studentTable order by StudentID)t
                    for xml path('')
                    ), 1, 2, '')
                    + ' from studentTable;';

exec(@sql);

Result

+------------+------------+------------+--------------+--------------+--------------+
| StudentID1 | StudentID2 | StudentID3 | StudentName1 | StudentName2 | StudentName3 |
+------------+------------+------------+--------------+--------------+--------------+
| 125        |  225       |  325       | A            |   B          |  C           |     
+------------+------------+------------+--------------+--------------+--------------+