First of all, you need to unpivot data. See:
Using PIVOT and UNPIVOT - SQL Server | Microsoft Learn[
^]
How?
Take a look at example:
SELECT RegdNo, Subject_code, AVG(Score) AS AvgScore
FROM
(
SELECT RegdNo, Subject_code, Descr, Score,
ROW_NUMBER() OVER(PARTITION BY RegdNo ORDER BY Score) AS RN
FROM
(
SELECT RegdNo, Subject_code, VAL1, VAL2, VAL3
FROM FinalMarks
) AS PVT
UNPIVOT( Score FOR Descr IN (VAL1, VAL2, VAL3)) AS unpvt
) AS FinQry
WHERE RN IN (1, 2)
GROUP BY RegdNo, Subject_code;
Sample:
SQL Server 2022 | db<>fiddle[
^]
Result:
RegdNo Subject_code AvgScore (due to:)
112023221 HPENG221 29 (28, 30)
112023225 HPMAT333 15 (10, 20)