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

Last Reply on Oct 18, 2017 03:08 AM By kalpesh

Posted on Oct 17, 2017 11:24 PM

 I want to add more data fields such as TotalMarks, Percentage, Grade, Remarks in the same way.How to do it?

Output should contain this

AdmissionNo | English |Math |Science |Urdu |ObtainMarks |TotalMarks| Percentage |Grade |Remarks

 

More over I want to display student details such as tblStd: AdmissionNo, SName,ReferenceNo,FName final output should like this  AdmissionNo |SName|ReferenceNo|FName| English |Math |Science |Urdu |ObtainMarks |TotalMarks| Percentage |Grade |Remarks

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'SELECT distinct AdmissionNo
                  , ' + @cols + '
                  ,ObtainMarks
            FROM (SELECT AdmissionNo
                         ,SubjectName
                         , Marks
                         ,(SELECT SUM(tsm.Marks)
                           FROM #tblSetMarks tsm
                           where tsm.AdmissionNo = sm.AdmissionNo ) as ObtainMarks
                 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

 

Posted on Oct 18, 2017 03:08 AM Modified on on Oct 18, 2017 03:15 AM

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

AdmissionNo English Math Science Urdu ObtainedMarks TotalMarks Percentage Grade
1 15 15 15 15 60 200 30 FAIL
2 25 25 25 25 100 200 50 D