SQL Server query issue based on the column value populate the result in other column. We can add date [closed]

In the attached scenario, one of the column “result” is showing fail in one of the row and remaining all scenarios showing it as pass. In this case, I need to populate in “status” column i need to show it as fail. if user passes in all subjects then only in status column against all subjects we need to show it as PASS.

Answer

You can simply use a window function to detect if a student has been failed in a subject, then set the Status to 'Fail' as the following:

SELECT *,
       CASE WHEN
                 SUM(CASE WHEN Result = 'Fail' THEN 1 ELSE 0 END) 
                       OVER(PARTITION BY StudentId ORDER BY StudentId)
                 = 0
            THEN 'Pass' 
            ELSE 'Fail' 
       END Status
FROM
(
  VALUES
  (1, 'Subject1', 'Fail'),
  (1, 'Subject2', 'Pass'),
  (1, 'Subject3', 'Pass'),
  (2, 'Subject1', 'Pass'),
  (2, 'Subject2', 'Pass'),
  (2, 'Subject3', 'Pass'),
  (3, 'Subject1', 'Fail'),
  (3, 'Subject2', 'Fail'),
  (3, 'Subject3', 'Fail')
) T(StudentId, Subject, Result);