TSQL: Split one column into two based on value
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!
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