Sql Query Fields As Columns

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 |Geor

Solution 1:

Try,

SELECT  a.Student_name,
        MAX(CASEWHEN subject ='MATH'THEN grade ELSENULLEND) MathGrade,
        MAX(CASEWHEN subject ='ENGLISH'THEN grade ELSENULLEND) EnglishGrade,
        MAX(CASEWHEN subject ='History'THEN grade ELSENULLEND) HistoryGrade
FROM    Student a
        LEFTJOIN Grade b
            ON a.Student_ID = b.Student_ID
GROUPBY a.Student_name

SQLFiddle Demo

Solution 2:

While @John's answer will work if you have a known number of subjects, if you have an unknown number of subjects then you can use prepared statements to generate this dynamically. Here is a good article:

Dynamic pivot tables (transform rows to columns)

Your code would look like this:

SET@sql=NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when Subject = ''',
      Subject,
      ''' then Grade end) AS ',
      Subject
    )
  ) INTO@sqlFROM grade;

SET@sql= CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM student s
                   LEFT JOIN grade AS g 
                    ON s.student_id = g.student_id
                   GROUP BY s.Student_name');

PREPARE stmt FROM@sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

See SQL Fiddle With Demo