Hey smile,
Please refer below query.
SQL
CREATE TABLE #tblDefSubjects (SubjectID INT, SubjectName VARCHAR(50))
INSERT INTO #tblDefSubjects VALUES(1,'English')
INSERT INTO #tblDefSubjects VALUES(2,'Urdu')
INSERT INTO #tblDefSubjects VALUES(3,'Math')
CREATE TABLE #tblAssignSubjecttoClass (ID INT,[Session] VARCHAR(50),Class VARCHAR(10),SubjectID INT)
INSERT INTO #tblAssignSubjecttoClass VALUES(90,2019,'One',1)
INSERT INTO #tblAssignSubjecttoClass VALUES(91,2019,'One',2)
INSERT INTO #tblAssignSubjecttoClass VALUES(92,2019,'One',3)
CREATE TABLE #tblSubjectAllocationtoTeacher ([Session] VARCHAR(50),Class VARCHAR(10),Section VARCHAR(10),SubjectID INT,TeacherName VARCHAR(20))
INSERT INTO #tblSubjectAllocationtoTeacher VALUES(2019,'One','A',90,'ABC')
INSERT INTO #tblSubjectAllocationtoTeacher VALUES(2019,'One','A',91,'SKY')
INSERT INTO #tblSubjectAllocationtoTeacher VALUES(2019,'One','A',92,'XYZ')
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(SubjectName) from #tblDefSubjects as sal
inner join #tblAssignSubjecttoClass as si on sal.SubjectID=si.SubjectID
inner join #tblSubjectAllocationtoTeacher as ds on si.ID=ds.SubjectID
group by SubjectName order by SubjectName FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = ';WITH CTE AS(
select tda.Session,dts.Class, dts.Section, dc.SubjectName, dts.TeacherName
from #tblSubjectAllocationtoTeacher as dts
inner join #tblAssignSubjecttoClass tda on tda.Id = dts.SubjectId
inner join #tblDefSubjects as dc on tda.SubjectId=dc.SubjectId
)
SELECT Session,Class,Section,' + @cols + ' from CTE
pivot(max(TeacherName) for SubjectName in (' + @cols + ')) p '
execute(@query);
DROP TABLE #tblDefSubjects
DROP TABLE #tblAssignSubjecttoClass
DROP TABLE #tblSubjectAllocationtoTeacher
Output
Session Class Section English Urdu Math
2019 One A ABC SKY XYZ
