# 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!

## Answers

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