Filter rows in dynamic pivot query in SQL Server

Hi smile,

Please refe below query,

SQL

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')

DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','')+ QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblTestSystem ) AS FeesHead

SET @DynamicPivotQuery2 = '
;WITH CTE AS(
	SELECT RoundID,fa.AdmissionNo,Marks,SubjectID,ClassID
	,(SELECT SUM(tsm.Marks) FROM tblTestSystem tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID ) as Obtain
	,(SELECT SUM(tsm.TotalMarks) FROM tblTestSystem tsm where tsm.AdmissionNo = fa.AdmissionNo AND tsm.RoundID = fa.RoundID) as Total
	FROM tblTestSystem 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);


DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblTestSystem ) AS FeesHead

DECLARE @SumColumnName AS NVARCHAR(MAX)
SELECT @SumColumnName = ISNULL(@SumColumnName + ',SUM(','')+ QUOTENAME(SubjectID) + ') AS '+ QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblTestSystem ) AS FeesHead
SET @SumColumnName = 'SUM(' + @SumColumnName

SET @DynamicPivotQuery = '
;WITH CTE AS(
	SELECT RoundID,fa.AdmissionNo,Marks,SubjectID,ClassID
	,(SELECT SUM(tsm.Marks) FROM tblTestSystem tsm where tsm.AdmissionNo = fa.AdmissionNo ) as Obtain
	,(SELECT SUM(tsm.TotalMarks) FROM tblTestSystem tsm where tsm.AdmissionNo = fa.AdmissionNo) as Total
	FROM tblTestSystem as fa
)
SELECT AdmissionNo,ClassID,'+@SumColumnName+',Obtain,Total FROM CTE
PIVOT (MAX(Marks)
FOR SubjectID IN('+@ColumnName+')) p GROUP BY AdmissionNo,ClassID,Obtain,Total order by AdmissionNo asc '
EXEC(@DynamicPivotQuery);

Output 1

Output 2