TSQL: Split one column into two based on value

Question

I'm not quite sure what I need to be asking to solve this problem, ie. whether this is subquery, group by, union/join question, or something else entirely...

I have a student results table setup something like this:

student gender class result
------- ------ ----- ------
Bob     M      Math  A+
Mary    F      Math  A+
Peter   M      Math  A+
Jane    F      Math  B

What I want to do is to be able to see the results aggregated by class, but see the gender differences. Eg (based on the above example table):

males females class result
----- ------- ----- ------
2     1       Math  A+
0     1       Math  B

I hope my question makes sense and any help would be appreciated!

Answer 1

The query you need would look like this, using SUM() aggregations, and a GROUP BY

SELECT SUM(CASE
              WHEN Gender = 'M' THEN 1
              ELSE 0
           END) AS Males
   , SUM(CASE
            WHEN Gender = 'F' THEN 1
            ELSE 0
         END) AS Females
   , class
   , result
FROM student
GROUP BY class, result