If I am reverse engineering the SQL Join expression correctly, you have:
A classic many to many relationship. The records link by ID fields with ClassStudent being the "many" side of two relationships.
The SELECT part indicates that you just want to list the student's name and the year from the related class record.
Your WHERE clause specififies that this only be for records where the class is in one of two categories and a type field in the join table is 2.
Base your report layout on ClassStudent. In manage | database you will need to have defined the three tables and linked them in relationships by the ID fields specified in your Join clause.
The next step can be scripted or you can do it manually:
Enter Find mode
Specify 2 in the type field and "alpha" in the Class::Category field. Create a second find request. Specify 2 in the Type field and "Beta" in the Class::Category field.
Perform the find.
Use Sort Records to sort them in the desired order by Class::Year and then by Student Last and first names.
A manual find requires that you have such fields present on your layout so that you can enter criteria. This need not be the case in a scripted find.
Here's a thread on scripted finds that you may find helpful: Scripted Find Examples