Selecting values from one column based on a different columns value

Having trouble stating my problem succinct开发者_高级运维ly here, so I'll just give an example.

Let's say I have a DB2 table about Students:

Name           Class          Grade
Billy J        Econ           A
Sarah S        Maths          B
Greg X         Computes       A-
Billy J        Maths          D
Greg X         Maths          C+

And I want to retrieve those students that are in both Econ and Maths, and display the information thusly:

Name       Maths Grade       Econ Grade
Billy J    D                 A

How in the world can I accomplish this?

---

This solution will solve the problem for the two classes you named:

SELECT Name, Math.Grade AS MathsGrade, Econ.Grade AS EconGrade
  FROM Students Math INNER JOIN Students Econ ON Math.Name = Econ.Name
  WHERE Math.Class = 'Maths' AND Econ.Class = 'Econ'

The only thing that this solution doesn't do is include the spaces in your derived column names. You can do that by writing Maths Grade and Econ Grade in whatever characters DB2 uses for identifier quotes.

To be included students must have both a Maths and an Econ grade.

---

SELECT * from Students
where id in 
(SELECT id from Students where Class = 'Econ')
AND id in
(SELECT id from Students where Class = 'Math');