SQL Query Fields as Columns

Question

I really don't know how to do this, but please kindly check the details below.

Student

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

the class

 |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 am doing this in hardcover, filling in an unrelated datagrid with the first column name, then the student name, and then adding details for each student name. This takes a lot of time, and I want to optimize the query better.

Thanks in advance.

Answer 1

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

Dynamic pivot tables (converting rows to columns)

Your code will look like this:

 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(case when Subject = ''', Subject, ''' then Grade end) AS ', Subject ) ) INTO @sql FROM 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; DEALLOCATE PREPARE stmt; 

See SQL Fiddle With Demo

Answer 2

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