clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811935 fiddles created (25312 in the last week).

SET DATEFORMAT dmy; CREATE TABLE A ( [Stud#] varchar(30), Dept varchar(30), [Status] varchar(30), Marks int, DOB date ); INSERT INTO A ([Stud#], Dept, [Status], Marks, DOB) VALUES('100', 'ABC', 'FAIL', 30, '12-12-1990'), ('100', 'CDE', 'PASS', 80, '10-10-1995'), ('101', 'XXX', 'FAIL', 20, '11-12-1990'), ('101', 'YYY', 'PASS', 90, '10-12-1985');
4 rows affected
 hidden batch(es)


SELECT [Stud#], MAX(Dept) AS Dept, MAX(SubDept) AS SubDept, MAX(Marks) AS Marks, MAX(DOB) As DOB FROM ( SELECT [Stud#], Dept, NULL SubDept, 0 As Marks, DOB FROM A WHERE [Status] = 'FAIL' UNION ALL SELECT [Stud#], NULL Dept, Dept AS SubDept, Marks, DOB FROM A WHERE [Status] = 'PASS' ) Final GROUP BY [Stud#]
Stud# Dept SubDept Marks DOB
100 ABC CDE 80 1995-10-10
101 XXX YYY 90 1990-12-11
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)