mysql - SQL Query fields as columns

Question

I dont really know how to put this but please kindly check the details below.

Student

|Student_ID|Student_Name|
|1         |Ryan        |
|2         |Camille     |
|3         |George      |

Grade

|Student_ID|Subject |Grade
|1         |Math    |5 
|1         |English |3 
|1         |History |1
|2         |Math    |3 
|2         |English |4 
|2         |History |1 
|3         |Math    |5 
|3         |English |1 
|3         |History |2 

Is it possible to get this result?

Student_Name|Math|English|History
Ryan        |5   |3      |1
Camille     |3   |4      |1
George      |5   |1      |2

Now I've been doing this the hardway by populating an unbound datagrid with first the column name, then the student name then adding the the details for each student name. This is time consuming and I want to optimize the query better.

Thanks in advance.

Answer 1

Try,

SELECT  a.Student_name,
        MAX(CASE WHEN subject = 'MATH' THEN grade ELSE NULL END) MathGrade,
        MAX(CASE WHEN subject = 'ENGLISH' THEN grade ELSE NULL END) EnglishGrade,
        MAX(CASE WHEN subject = 'History' THEN grade ELSE NULL END) HistoryGrade
FROM    Student a
        LEFT JOIN Grade b
            ON a.Student_ID = b.Student_ID
GROUP BY a.Student_name

SQLFiddle Demo