I have the following table structure in the database
|
ClassID
|
SectionID
|
SubjectID
|
TeacherID
|
STime
|
ETime
|
Days
|
|
One
|
A
|
English
|
A
|
8:00
|
9:00
|
1-4
|
|
One
|
A
|
G. Science
|
B
|
8:00
|
9:00
|
5-6
|
|
One
|
A
|
Urdu
|
C
|
9:00
|
10:00
|
1-3
|
|
One
|
A
|
G. Know
|
D
|
9:00
|
10:00
|
4-6
|
|
One
|
A
|
Math
|
E
|
10:00
|
11:00
|
1-6
|
and I want to display the data from database like this
Required Table:
|
ClassID
|
SectionID
|
8:00
|
9:00
|
10:00
|
|
One
|
A
|
English-Name:A
Days:1-4,
G.Science-
Name: B
Days:5-6
|
Urdu-Name:C Days:1-3,
G.Know-Name: D
Days: 4-6
|
Math- Name: E
Days: 1-6
|
and I wrote the following Query
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(SubjectName) from tblSubjectAllocation as sal
inner join tblAssignSubjects as si on sal.SubjectID=si.ASID
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 = ';WITH CTE AS(
select AcademicName,ClassName,SectionName,SubjectName,EmpName
from tblSubjectAllocation as sa
inner join tblAcademicYear as ay on sa.YearID=ay.YearID
inner join tblDefClass as dc on sa.ClassID=dc.ClassID
inner join tblDefSection as ds on sa.SectionID=ds.SectionID
inner join tblAssignSubjects as dss on sa.SubjectID=dss.ASID
inner join tblDefSubject as dssb on dssb.SubjectID=dss.SubjectID
inner join tblTeacher as emp on sa.TeacherID=emp.TeacherID
)
SELECT AcademicName,ClassName,SectionName,' + @cols + ' from CTE
pivot(max(EmpName) for SubjectName in (' + @cols + ')) p '
execute(@query);
but it is not showing me data according to my requirements.
How to fix this issue?
