Dynamic Pivot query Error: Invalid Column Name in SQL Server

You are using SubjectID as aggregate function inside the pivot function. So you can't select that column in the select query. Remove the SubjectID from the select statement.

Refer the below modified query.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX) 
select @cols = STUFF((SELECT ',' + QUOTENAME(ExamDate) from tblDateSheet group by ExamDate order by ExamDate FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = N'SELECT SetExamID,ClassID,SectionID,STime,ETime' + @cols + ' from
             (
                select SetExamID,ClassID,SectionID,SubjectID,STime,ETime,ExamDate
                from tblDateSheet) x
            pivot
  
            (
                max(SubjectID)
                for ExamDate in (' + @cols + ')
            ) p '
execute(@query);