Dynamic Pivot

Posted by Jeff Rix on December 23, 2011 under T-SQL | Be the First to Comment

Create a table with students, classes, and grades for multiple months.

 

if OBJECT_ID('tempdb..#Student_Grade') is not null drop table #Student_Grade;



create table #Student_Grade

(Student_ID int,

Student_Name varchar(30),

Class_ID int,

Class varchar(20),

Grade decimal(4,2),

Month_Name varchar(20),

Assignment_Type Char(1)

)


INSERT INTO #Student_Grade VALUES...

The records will have one column for month in this table. 

Notice there are two math grades for Will Smith in April.

Suppose we would like to take these months and create columns for them.

Instead of hardcoding the months use the stuff and xml path to get all 

available months in the table.

select

 stuff(

(select N','+quotename(Month_Name) as [text()]

from (

select distinct Month_Name from #Student_Grade) as Month_Name

order by Month_Name

for xml path('')),1,1,'')+ N')) as X;'

declare a variable to hold your results and use the average aggregate 

to get  the average of a students grade for a particular month and course.
DECLARE @sql AS NVARCHAR(1000);


SET @sql = N'select *

from (

	select cast(Student_ID as varchar(20)) + ''. '' + Student_Name as Student,

	cast(Class_ID as varchar(20)) + ''-'' + Class as Class,

	Grade, Month_Name

from #Student_Grade

) as Y

pivot(avg(grade) for Month_Name in ('+

 stuff(

(select N','+quotename(Month_Name) as [text()]

from (

select distinct Month_Name from #Student_Grade) as Month_Name

order by Month_Name

for xml path('')),1,1,'')+ N')) as X;';


select @sql as sql_code

This variable now contains the sql code that will pivot for each month

students  received grades.

select *  from (   select cast(Student_ID as varchar(20)) + '. ' + Student_Name as Student,

cast(Class_ID as varchar(20)) + '-' + Class as Class,   Grade, Month_Name  from #Student_Grade  ) as Y

pivot(avg(grade) for Month_Name in ([April],[February],[January],[March],[May])) as X;

Execute the sql code using exec sp_executesql

exec sp_executesql @stmt = @sql;

81.6 is the average of Will Smiths math grade for English in April.