How to pivot rows into columns MySQL

Question

Been looking to pivot rows (progress_check) into columns check 1, check 2 etc... No sum or totals required, just to display the results hopefully... Can anyone help, Thanks Ad

This is the table (grades) in MYSQL

Hope to get something like this!

Answer 1

This is one way to pivot using standard SQL (and the part of the standard that MySQL implements). That means it not only works in MySQL, but also in most SQL databases:

SELECT
 r0.sims_id, 
 r0.subject, 
 r1.result AS "C1",
 r2.result AS "C2",
 r3.result AS "C3"
FROM
 (SELECT DISTINCT 
 sims_id, subject
 FROM 
 results
 ) r0
 LEFT JOIN results r1 
 ON r1.sims_id = r0.sims_id AND r1.subject = r0.subject AND r1.progress_check = 'C1'
 LEFT JOIN results r2 
 ON r2.sims_id = r0.sims_id AND r2.subject = r0.subject AND r2.progress_check = 'C2'
 LEFT JOIN results r3 
 ON r3.sims_id = r0.sims_id AND r3.subject = r0.subject AND r3.progress_check = 'C3'
ORDER BY
 r0.sims_id, r0.subject ;

Check it at SQLFiddle

Although for this kind of problems (sims_id, subject-id, progress_check) should be a PRIMARY KEY (or, at least, UNIQUE), using this method, it there are repeated values for "C1", "C2" or "C3" for one single sims_id, subject... the cartesian product of all available information appears in the result. No information is lost, but it is not summarised either. Whether this behaviour is desirable or not depends on the use-case.

Answer 2

GROUP BY and using MAX or SUM is the most used standard pivot way.

SELECT 
 results.sims_id
 , results.subject 
 , MAX(CASE WHEN results.progress_check = "C1" THEN results.result END) "C1"
 , MAX(CASE WHEN results.progress_check = "C2" THEN results.result END) "C2"
 , MAX(CASE WHEN results.progress_check = "C3" THEN results.result END) "C3"
FROM 
 results
GROUP BY
 results.sims_id
 , results.subject 
ORDER BY
 results.sims_id ASC
 , results.subject ASC

Result

sims_id subject C1 C2 C3 
------- ------- ------ ------ --------
 1111 Art C B (NULL) 
 1111 English 6 5 (NULL) 
 1111 History B C (NULL) 
 1111 maths 8 8 (NULL) 
 1111 science A B (NULL) 
 2222 Art (NULL) A (NULL) 
 2222 English 6 (NULL) 
 2222 ICT A B (NULL) 
 2222 maths 7 6 (NULL) 
 2222 science A A* (NULL) 

see demo http://sqlfiddle.com/#!9/0be1f2/1