Crosstab Query With Dynamic Columns In Sql Server 2008

I'm having trouble with a Cross Tab query in SQL Server and was hoping that someone could please help? I have the following table: - Student ID - Name - Course - Course Level -

Solution 1:

The query you will need to get the results in your question is:

createtable StudentResults(StudentID int,Name nvarchar(50),Course nvarchar(50), CourseLevel nvarchar(10));
insertinto StudentResults values(1,'John','English','E2'),(1,'John','Maths','E3'),(1,'John','Computing','L2');

select StudentID
        ,Name
        ,[Computing]
        ,[Maths]
        ,[English]
from StudentResults
pivot(max(CourseLevel) for Course in([Computing],[Maths],[English])
     ) as p;

Output:

StudentID   Name    Computing   Maths   English
1           John    L2          E3      E2

Though as you may be able to work out, this requires hard coding the subjects. If your list of subjects is likely to change, then this query will no longer be fit for purpose.

If you are comfortable, you can remedy this with dynamic SQL:

declare@colsas  nvarchar(max)
       ,@queryas nvarchar(max);

set@cols= stuff(
                   (selectdistinct','+quotename(Course)
                    from StudentResults
                    for xml path(''),type).value('.','nvarchar(max)'
                   )
                 ,1,1,''
                 );

set@query='select StudentID
                    ,Name
                    ,'+@cols+'
            from StudentResults
            pivot (max(CourseLevel) for Course in ('+@cols+')
                  ) p';

execute (@query);

Ideally though, you would simply return a set of data, as it appears to be in your source table and let your reporting layer (SSRS for example) handle the pivoting, which it is much better suited towards than pure SQL.

Solution 2:

SELECT studentId,
StudentName,
English,
Maths,
Computing
FROM (
SELECT T.StudentId,
    T.StudentName,
    T.Course,
    T.CourseLevelFROM Test T
) AS J
PIVOT(MAX(CourseLevel) FOR Course IN (
        [English],
        [Maths],
        [Computing]
        )) AS P