I want to display class and section wise result
tblStdReg: AdmissionNo,ReferNo,RollNo,SName,FName,Class,Section
tblSetMarks:AdmissionNo,Class,Section,Subject,MaxMarks,Marks
my Query is
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME([SubjectName])
FROM (SELECT DISTINCT SubjectName FROM tblSetMarks as sm inner join tblDefSubject as ds on sm.SubjectID=ds.SubjectID) AS Course
DECLARE @qry NVARCHAR(4000)
SET @qry =
N'
;WITH cteStudentMarksDetails
AS ( SELECT TA.AdmissionNo
,(SELECT SName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
,(SELECT FName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
,(SELECT SPhone FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
,SUM(Marks) ObtainedMarks
,SUM([MaxMarks]) TotalMarks
,Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) as Percentage
,(CASE WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''A+''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''A''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''B''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''C''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''D''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''E''
ELSE ''FAIL'' END ) AS Grade
,(CASE WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''OutStanding''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''Excellent''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''Very Good''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''Good''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''Satisfactory''
WHEN Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''Work Hard''
ELSE ''FAIL'' END ) AS Remarks
FROM tblSetMarks TA
GROUP BY AdmissionNo
)
SELECT distinct AdmissionNo
,SName
,FName
,Phone
, ' + @cols + '
,ObtainedMarks
,TotalMarks
,Percentage
,Grade
,Remarks
FROM (SELECT AdmissionNo
,SubjectName
, Marks
,(SELECT ObtainedMarks FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as ObtainedMarks
,(SELECT Percentage FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as Percentage
,(SELECT TotalMarks FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as TotalMarks
,(SELECT Grade FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as Grade
,(SELECT Remarks FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as Remarks
,(SELECT SName FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as SName
,(SELECT FName FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as FName
,(SELECT Phone FROM cteStudentMarksDetails csd Where csd.AdmissionNo = sm.AdmissionNo) as Phone
FROM tblSetMarks as sm
inner join tblDefSubject as ds
on sm.SubjectID=ds.SubjectID) p
PIVOT (MAX(Marks) FOR SubjectName IN ('+@cols+')) AS Pvt'
EXEC sp_executesql @qry
now how to show class and section wise result???