Display records by date not in a group in SQL Server

kevinf
 
on Dec 08, 2022 09:56 PM
386 Views

I have a table containing information for students.

Id           PupilId     Class      Date           Group

1            10           Class 1   2022-12-09               

2            12           Class 5   2022-12-09

3            80           Class 1   2022-12-09 Groupname

4            86          Class 1   2022-12-09 Groupname

5            90           Class 1   2022-12-09 Groupname

6            60           Class 1   2022-12-09 New Group 

7            72           Class 1   2022-12-09 New Group

There is a field (pupilgroup="Testgroup" ) used to group the 50 students if they are in a group and if they aren't in a group its blank.

In SQL How do I display all students by date not in a group and only 1 from the TestGroup.

I would like it to display the Description for (student name, Class, room), show Group (blank) for the students not in a group and student name (blank), Class (Blank), room (blank), Group (Description) for the single entry.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
kevinf
 
on Dec 09, 2022 05:00 PM

Found Answer

SELECT a.Group, a.Id, a.PupilId, a.Class, a.Date
FROM Student as a
WHERE a.Group is null and a.date = '2022-12-09'
UNION
SELECT Distinct b.Group, "", "", "", ""
FROM Student as b
WHERE b.Group is not null and b.date = '2022-12-09'
ORDER BY date, Class;

Thanks for your help