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.

John Woo · Accepted Answer · 2012-10-01 04:58:39Z

11

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

answered Oct 1, 2012 at 4:58

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ruben_PH Over a year ago

Thanks, I'll incorporate this to what I am doing and will get back to you for the result.

Ruben_PH Over a year ago

I noticed that you predefined the subjects name in the query, the system can add more subjects than those given above. Is there any way to not have predefined queries? Sorry for my english.

Ruben_PH Over a year ago

Ok, this solves my question, I just have to play with it a little. Thanks.

Taryn · Accepted Answer · 2012-10-01 14:25:55Z

3

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

answered Oct 1, 2012 at 14:25

Taryn

249k57 gold badges374 silver badges409 bronze badges

2 Comments

Ruben_PH Over a year ago

I've managed to work with @John's answer by getting the column name count via the .net then populate it with another dataset. Then I proceed to populate another dataset with the rows. But your answer was amazing. Wow, just wow. Thanks.

Ruben_PH Over a year ago

@bluefeet, I've managed to use your answer and convert it to a stored procedure. Thank you very much.

Collectives™ on Stack Overflow

SQL Query fields as columns

2 Answers 2

SQLFiddle Demo

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related