Display student result like Marks, Obtained mark, Total mark, Percentage, Grade using dynamic pivot query in SQL Server

Hi smile,

Refer the below test query for your reference.

SQL

CREATE TABLE #tblSetMarks(AdmissionNo INT, SubjectID INT,Marks INT,[Max] INT)
CREATE TABLE #tblDefSubject(SubjectID INT, SubjectName VARCHAR(20))
 
INSERT INTO #tblSetMarks (AdmissionNo , SubjectID ,Marks,[Max] )
SELECT 1,1,15,50
UNION ALL
SELECT 1,2,15,50
UNION ALL
SELECT 1,3,15,50
UNION ALL
SELECT 1,4,15,50
UNION ALL
SELECT 2,1,25,50
UNION ALL
SELECT 2,2,25,50
UNION ALL
SELECT 2,3,25,50
UNION ALL
SELECT 2,4,25,50
 
 
INSERT INTO #tblDefSubject
VALUES (1,'English')
,(2,'Math')
,(3,'Science')
,(4,'Urdu')
 
  
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 AdmissionNo
			     ,SUM(Marks) ObtainedMarks
				 ,SUM([Max]) TotalMarks
				 ,Round((SUM(Marks)) * 100/ SUM([Max]),1) as Percentage
				 ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 90 THEN ''A+''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 80 THEN ''A'' 
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 70 THEN ''B''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 60 THEN ''C''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 50 THEN ''D'' 
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 40 THEN ''E''
					    ELSE ''FAIL'' END ) AS Grade
				 ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 90 THEN ''OutStanding''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 80 THEN ''Excellent''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 70 THEN ''Very Good''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 60 THEN ''Good''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 50 THEN ''Satisfactory''
					    WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 40 THEN ''Work Hard''
					    ELSE ''FAIL'' END ) AS Remarks
				 FROM #tblSetMarks TA
				 GROUP BY AdmissionNo
			  )


		  SELECT distinct AdmissionNo
                  , ' + @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
                 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
 
DROP TABLE #tblSetMarks
DROP TABLE #tblDefSubject

Output