DYNAMIC PIVOT & UNPIVOT
DYNAMIC PIVOT
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AssignmentName)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT StudentName, ' + @cols + ' from
(
select StudentName, AssignmentName, grade
from yourtable
) x
pivot
(
min(grade) for assignmentname in (' + @cols + ')
) p '
execute(@query)
http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns
