I write the following query. and the datatype for ExamDate is varchar(50)
DECLARE @cols AS NVARCHAR(MAX),
@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 = 'SELECT ExamType,ClassName,SectionName,STime,' + @cols + ' from
(
select ExamType,ClassName,SectionName,SubjectName,STime,ExamDate
from tblDateSheet as dts inner join tblDefClass as dc on dts.ClassID= dc.ClassID inner join tblDefSection as ds on dts.SectionID=ds.SectionID
inner join tblAssignSubjects as si on dts.SubjectID=si.ASID inner join tblDefSubject as dsb on si.SubjectID=dsb.SubjectID
inner join tblSetExam as se on dts.SetExamID= se.SetExamID ) x
pivot
(
max(SubjectName)
for ExamDate in (' + @cols + ')
) p '
execute(@query);
it is showing output like this
but i want output like this
ClassName
|
Section
|
03.04.2019
|
04.04.2019
|
05.04.2019
|
One
|
A
|
Eng
|
Urdu
|
Math
|