TSQL Dynamic Pivot Table

I am using a select statement like this...

SELECT FileYear, FileSemester, StudentID, AssessmentCode, AssessAreaHdgAbbrev1, AssessResultsResult
FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) AND (FileSemester = 2) AND (assessmentCode LIKE '11%') AND (AssessAreaHdgAbbrev2 = 'Grade')

This gives me

FileYear  FileSemester   StudentID   assessmentCode   AssessAreaHdgAbbrev1  AssessResultsResult
-------------------------------------------------------------------------------------------    
2014        2             41965        11ENGADV        Ass1                       C
2014        2             41965        11ENGADV        Ass2                       C
2014        2             41965        11ENGADV        Ass3                       C
2014        2             41965        11ENGADV        Prelim                       
2014        2             41965        11HISANC        Ass1                       A
2014        2             41965        11HISANC        Ass2                       B
2014        2             41965        11HISANC        Ass3                       B
2014        2             41965        11HISANC        Prelim                       
2014        2             41965        11HISMOD        Ass1                       B
2014        2             41965        11HISMOD        Ass2                       B
2014        2             41965        11HISMOD        Ass3                       
2014        2             41965        11HISMOD        Prelim                       
2014        2             41965        11MATGEN        Ass1                       B
2014        2             41965        11MATGEN        Ass2                       
2014        2             41965        11MATGEN        Prelim                       
2014        2             41965        11PEDPDH        Ass1                       B
2014        2             41965        11PEDPDH        Ass2                       B
2014        2             41965        11PEDPDH        Ass3                       
2014        2             41965        11PEDPDH        Ass4                       
2014        2             41965        11PEDPDH        Prelim                       

I would like to dynamically pivot the data to give me something which looks like

FileYear  FileSemester  StudentID  AssessmentCode  Ass1  Ass2  Ass3  Ass4  Prelim 
---------------------------------------------------------------------------------
2014        2            41965       11ENGADV        C    C    C        
2014        2            41965       11HISANC        A    B    B        
2014        2            41965       11HISMOD        B    B    
2014        2            41965       11MATGEN        B    
2014        2            41965       11PEDPDH        B    B    

Note that it has to be dynamic as the number of assessment points may change from subject to subject and year to year etc.

I have looked at information on this and tried to put together a dynamic pivot like

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

select @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(AssessAreaHdgAbbrev1)
FROM (SELECT DISTINCT AssessAreaHdgAbbrev1 
FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) AND (FileSemester = 2) AND (AssessAreaHdgAbbrev2 = 'Grade') AND (AssessmentCode LIKE '11%')) AS Courses


SET @DynamicPivotQuery = N'
SELECT FileYear,  FileSemester, StudentID, AssessmentCode, AssessAreaHdgAbbrev1, AssessResultsResult
FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) AND (FileSemester = 2) AND (AssessAreaHdgAbbrev2 = ''grade'') AND (AssessmentCode LIKE ''11%'')
    PIVOT
    (
    MAX(AssessResultsResult) 
          FOR AssessAreaHdgAbbrev1 IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

I keep getting error messages like

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'PIVOT'.

I am assuming this is because of the quotation marks or the where statement syntax. I am getting a little frustrated and was just wondering if anyone could have a look at my code and tell me how I could dynamically pivot the data so that it gives the Grades in columns rather than in separate rows.

Any help would be much appreciated.


Update

Thanks so much for your help.

My code now looks like...

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

select @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(AssessAreaHdgAbbrev1)
FROM (SELECT DISTINCT AssessAreaHdgAbbrev1 
FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) AND (FileSemester = 2) AND (AssessAreaHdgAbbrev2 = 'Grade') AND (AssessmentCode LIKE '11%')) AS Courses

SET @DynamicPivotQuery = N'
SELECT 
FileYear,  
FileSemester, 
StudentID, 
AssessmentCode, 
AssessAreaHdgAbbrev1, 
AssessResultsResult
FROM (
SELECT * FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) 
    AND (FileSemester = 2) 
    AND (AssessAreaHdgAbbrev2 = ''grade'') 
    AND (AssessmentCode LIKE ''11%'')) src
PIVOT (MAX(AssessResultsResult) FOR AssessAreaHdgAbbrev1 IN (' + @ColumnName + ')) AS   PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

when I run this I get

Msg 207, Level 16, State 1, Line 7
Invalid column name 'AssessAreaHdgAbbrev1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'AssessResultsResult'.

Obviously it is not recognizing the fields for some reason. Any help would be greatly appreciated.


ANSWERS:


Try:

SELECT StudentID, '+@ColumnName+'
FROM (
SELECT * FROM vStudentReportsSemesterResults
WHERE (FileYear = 2014) 
    AND (FileSemester = 2) 
    AND (AssessAreaHdgAbbrev2 = ''grade'') 
    AND (AssessmentCode LIKE ''11%'')) src
PIVOT (MAX(AssessResultsResult) FOR AssessAreaHdgAbbrev1 IN (' + @ColumnName + ')) AS PVTTable

Generaly: SELECT * FROM (SELECT foo) AS Foo PIVOT ...

Look at documentation for PIVOT:

USE AdventureWorks2008R2;

GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY VendorID;


 MORE:


 ? Dynamic Linq Query from Datatable for Pivot Table
 ? SQL Server Dynamic Pivot Query
 ? store the result of the a dynamic pivot to a temp table
 ? sql server dynamic pivoting
 ? sql server dynamic pivoting
 ? sql server dynamic pivoting
 ? Pivots with dynamic columns in SQL Server
 ? SQL Server : dynamic pivot over 5 columns
 ? sql server 2008 pivot table
 ? SQL - Combining Dynamic SQL with Pivot and Full Join