Dynamic Pivot with Joins in SQL Server

Last Reply 8 months ago By pandeyism

Posted 8 months ago

tblDefSubjects

SubjectID

SubjectName

1

English

2

Urdu

3

Math

tblAssignSubjectto Class

ID

Session

Class

SubjectID

90

2019

One

1

91

2019

One

2

92

2019

One

3

tblSubjectAllocationtoTeacher

Session

Class

Section

SubjectID

TeacherName

2019

One

A

90

ABC

2019

One

A

91

SKY

2019

One

A

92

XYZ

Required Output

Session

Class

Section

English

Urdu

Math

2019

One

A

ABC

SKY

XYZ

my query 

           DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
           select @cols = STUFF((SELECT ',' + QUOTENAME(SubjectName) from tblSubjectAllocation as sal inner join tblAssignSubjects as si on sal.SubjectID=si.SubjectID 
           inner join tblDefSubject as ds on si.SubjectID=ds.SubjectID
           group by SubjectName order by SubjectName FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
                   ,1,1,'')

           set @query = 'SELECT ClassName,SectionName' + @cols + ' from
                         (
                           select ClassName,SectionName,SubjectName,EmpName from tblSubjectAllocation as dts
                           inner join tblDefClass as dc on dts.ClassID=dc.ClassID
                       pivot
                       (
                           max(EmpName)
                           for SubjectName in (' + @cols + ')
                       ) p '
           execute(@query);

it is showing output with missing teacher Name and header Name "Section"

Posted 8 months ago

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