mysql select query help like in shown pictures

I have a table in mysql like shown below in p开发者_如何学Cicture 1:

Can you tell me how to make a select query which result will be like in picture 2

and to select faster:

Assumes that you have a unique constraint on STUD_ID,CLASS,LESSON

SELECT STUD_ID, 
       CLASS,
        MAX(CASE WHEN LESSON='MATH' THEN MARK END) AS MATH,
        MAX(CASE WHEN LESSON='CHEM' THEN MARK END) AS CHEM,

        ...
        AVG(MARK) AS `Avg`
GROUP BY STUD_ID, CLASS

(If you really need that ID column that doesn't relate to any ids you can use the technique from here).