How to pivot rows into columns MySQL
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
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.