SQL Server: Calculate dynamic column with total using PIVOT

Hi smile,

Please refer below sql query.

SQL

CREATE TABLE tblDefSubjects
(
   [SubjectId] [int] ,
   [SubjectName] [varchar](50)
) 

INSERT INTO tblDefSubjects VALUES (201,'English')
INSERT INTO tblDefSubjects VALUES (202,'Urdu')
INSERT INTO tblDefSubjects VALUES (203,'Math')

CREATE TABLE tblTestSystem
(
    AdmissionNo INT,
    ClassID     INT,
    SubjectID   INT,
    RoundID     INT,
    Marks       INT,
    TotalMarks  INT,
    TestDate    VARCHAR(50)
)
TRUNCATE TABLE tblTestSystem
INSERT INTO tblTestSystem VALUES(1,101,201,1,5,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,1,7,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,1,8,10,'1/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,1,6,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,1,2,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,1,5,10,'2/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,1,9,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,1,8,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,1,6,10,'3/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,201,2,5,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,2,7,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,2,8,10,'4/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,2,6,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,2,2,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,2,5,10,'5/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,2,9,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,2,8,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,2,6,10,'6/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,201,3,5,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,201,3,7,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,201,3,8,10,'7/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,202,3,6,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,202,3,2,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,202,3,5,10,'8/3/2023')
INSERT INTO tblTestSystem VALUES(1,101,203,3,9,10,'9/3/2023')
INSERT INTO tblTestSystem VALUES(2,101,203,3,8,10,'9/3/2023')
INSERT INTO tblTestSystem VALUES(3,101,203,3,6,10,'9/3/2023')
 
SELECT t1.admissionno,t1.classid,CONCAT(t2.SubjectName,'(',t1.TotalMarks,')') AS subjectid,t1.roundid,t1.marks,t1.totalmarks,t1.testdate INTO tblTestSystem2 FROM  tblTestSystem t1 join tblDefSubjects t2 ON t2.SubjectId = t1.SubjectID

DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','')+ QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblTestSystem2 ) AS FeesHead
 
SET @DynamicPivotQuery2 = '
;WITH CTE AS(
    SELECT RoundID,fa.AdmissionNo,Marks,SubjectID,ClassID
    ,(SELECT SUM(tsm.Marks) FROM tblTestSystem2 tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID ) as Obtain
    ,(SELECT SUM(tsm.TotalMarks) FROM tblTestSystem2 tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID) as Total
    FROM tblTestSystem2 as fa
)
SELECT AdmissionNo,ClassID,RoundID,'+@ColumnName2+',Obtain,Total FROM CTE
PIVOT (MAX(Marks)
FOR SubjectID IN('+@ColumnName2+')) p order by AdmissionNo asc'
EXEC(@DynamicPivotQuery2);

Output