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