I have a table like this
| Class | Subject | ExamDate | STime | ETime | 
| One | Eng | 11.04.2019 | 8:00 | 10:00 | 
| One | Urdu | 12.04.2019 | 8:00 | 10:00 | 
| One | Math | 13.04.2019 | 8:00 | 10:00 | 
I wrote the following dynamic pivot query:
 
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(SubjectName) from tblDateSheet as dts inner join tblAssignSubjects as si on dts.SubjectID=si.ASID
inner join tblDefSubject as dsb on si.SubjectID=dsb.SubjectID
group by ExamDate,SubjectName order by ExamDate FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ROW_NUMBER() OVER(ORDER BY(AdmissionNo))Row,SPic,AdmissionNo,SName,FName,FPhone,ExamType,ClassName,SectionName,STime,ETime,' + @cols + ' from
(
select SPic,AdmissionNo,SName,FName,FPhone,ExamType,ClassName,SectionName,SubjectName,STime,ETime,ExamDate
from tblDateSheet as dts inner join tblSetExam as se on dts.SetExamID=se.SetExamID
inner join tblStdReg as sr on dts.ClassID=sr.ClassID and dts.SectionID=sr.SectionID and Active_Status=''Active''
inner join tblDefClass as dc on dts.ClassID=dc.ClassID
inner join tblDefSection as ds on ds.SectionID=dts.SectionID
inner join tblAssignSubjects as si on dts.SubjectID=si.ASID
inner join tblDefSubject as dsb on si.SubjectID=dsb.SubjectID
) x
pivot
(
max(ExamDate)
for SubjectName in (' + @cols + ')
) p '
execute(@query);
it is showing me output like this
| Class | STime | ETime | Eng | Urdu | Math | 
| One | 8:00 | 10:00 | 2019-04-11 00:00:00:000 | 2019-04-12 00:00:00:000 | 2019-04-13 00:00:00:000 | 
whereas I also want to show Day Name along with date like this
| Class | STime | ETime | Eng | Urdu | Math | 
| one | 8:00 | 10:00 | 11.04.2019 [Thursday] | 12.04.2019 [Friday] | 13.04.2019 [Saturday] | 
i wrote to add ,DATENAME(weekday,ExamDate) as [Day] but it is showing me error. how to get desired output??