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??