I have a Marks table where Student's Quiz Marks and Assignment marks are entered in the following way

Now I want to select the highest 3 quiz numbers and the highest 3 assignment numbers and sum them as the total quiz and total assignment numbers respectively for each student. I have bold highest numbers in the above table for a better understanding

The result should be something like this

What I have Tried is unpivot technique but the result is not what I am expecting. Here's what I tried

SELECT TOP(3) StudentID,  Marks 
FROM
(SELECT StudentID,CourseID , Quiz1, Quiz2, Quiz3, Quiz4, Quiz5  FROM Marks) stu

UNPIVOT

(Marks FOR QuizNo IN (Quiz1, Quiz, Quiz3, Quiz4, Quiz5)) AS mrks 


WHERE    (CourseID = 321)
Order by Marks Desc

CodePudding user response：

One way is to unpivot and sum marks with CROSS APPLY

SELECT StudentID, CourseID, qm.m, am.m
FROM Marks 
cross apply (
  select sum (q) m
  from (
     select top(3) q
     from (
       values
         (Quiz1),(Quiz2),(Quiz3),(Quiz4),(Quiz5)
       ) t(q)
     order by q desc
     ) tq
  ) qm
cross apply (
  select sum (q) m
  from (
     select top(3) q
     from (
       values
         (Assignment1),(Assignment2),(Assignment3),(Assignment4),(Assignment5)
       ) t(q)
     order by q desc
     ) tq
  ) am