DECLARE @listSubjects VARCHAR(MAX) = null; WITH SubjectName AS ( SELECT distinct Subject.SubjectName AS SchoolSubject from Subject )SELECT @listSubjects = COALESCE(@listSubjects+'],[' ,'') + CAST(SchoolSubject AS Varchar)FROM SubjectName
DECLARE @listSubjects VARCHAR(MAX) = null; WITH SubjectName AS( SELECT distinct Subject.SubjectName AS SchoolSubject from Subject )SELECT @listSubjects = COALESCE(@listSubjects+'],[' ,'') + CAST(SchoolSubject AS Varchar) FROM SubjectName PRINT '[' + @listSubjects + ']' SELECT S.StudentNumber, S.StudentSurname, S.StudentFirstNames, S.SchoolYear, S.Class, --SJ.Class, --SJ.Section, M.SubjectCode, M.AssessmentPeriod, M.SubjectMaxima, MarksObtained, SJ.SubjectName into #temp from Students S INNER JOIN MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class INNER JOIN Subject SJ ON M.SubjectCode= SJ.SubjectCode where M.SchoolYear= '2020-2021' and M.Class= '1ere LIT' and M.AssessmentPeriod= '2è P' order by S.StudentSurname SELECT StudentNumber, StudentSurname, StudentName, ['+@listSubjects+'] FROM ( select StudentNumber, StudentSurname, StudentFirstNames as StudentName, MarksObtained, SubjectName from #temp ) SRC PIVOT ( SUM(MarksObtained) FOR SubjectName in (['+@listSubjects+']) ) PVT order by StudentSurname
DECLARE @listSubjects VARCHAR(MAX) = null; WITH SubjectName AS ( SELECT distinct Subject.SubjectName AS SchoolSubject from Subject ) SELECT @listSubjects = COALESCE(@listSubjects+'],[' ,'') + CAST(SchoolSubject AS Varchar) FROM SubjectName SELECT S.StudentNumber, S.StudentSurname, S.StudentFirstNames, S.SchoolYear, S.Class, --SJ.Class, --SJ.Section, M.SubjectCode, M.AssessmentPeriod, M.SubjectMaxima, MarksObtained, SJ.SubjectName into #temp from Students S INNER JOIN MarksEntry M ON S.StudentNumber=M.StudentNumber and S.Class= M.Class INNER JOIN Subject SJ ON M.SubjectCode= SJ.SubjectCode where M.SchoolYear= '2020-2021' and M.Class= '1ere LIT' and M.AssessmentPeriod= '2è P' order by S.StudentSurname DECLARE @sql NVARCHAR(MAX) = N' SELECT StudentNumber, StudentSurname, StudentName, ( SELECT distinct Subject.SubjectName from Subject ) ' SET @sql = @sql + N', FROM ( select StudentNumber, StudentSurname, StudentFirstNames as StudentName, MarksObtained, SubjectName from #temp ) SRC 'SET @sql = @sql +N'PIVOT (SUM(MarksObtained) FOR SubjectName in ([' + @listSubjects + N']) ) 'SET @sql = N' AS PVT order by StudentSurname'