Display subject wise mark for each student with total mark using dynamic Pivot query in SQL Server

Hi smile,

Refer the below test query for your reference and implement it in your code as per your code logic.

SQL

CREATE TABLE #tblSetMarks(AdmissionNo INT, SubjectID INT,Marks INT)
CREATE TABLE #tblDefSubject(SubjectID INT, SubjectName VARCHAR(20))

INSERT INTO #tblSetMarks (AdmissionNo , SubjectID ,Marks )
SELECT 1,1,15
UNION ALL
SELECT 1,2,15
UNION ALL
SELECT 1,3,15
UNION ALL
SELECT 1,4,15
UNION ALL
SELECT 2,1,25
UNION ALL
SELECT 2,2,25
UNION ALL
SELECT 2,3,25
UNION ALL
SELECT 2,4,25


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

OutPut