6

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!

4

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.