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

Need Your Help

Richfaces: Issue with HTML generation of datascroller/datatable

richfaces richdatatable

I'm using RichFaces 3.3.1 with JSF RE 1.2 and facelets.

Merge arrays together based on different values

php arrays merge

I am having trouble thinking of the logic with the following problem:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.