sql表记录值变成字段（转）

表中记录变字段：

表 a_a  ：

student_id	student_name	subject	grade
20070001	mark	math	88
20070001	mark	chinese	90
20070001	mark	english	95
20070002	green	math	96
20070002	green	chinese	92
20070002	green	english	91

如何由上表a_a 得到下表a

student_id	student_name	math	chinese	english
20070001	mark	88	90	95
20070002	green	96	92	91

实现的sql语句：

select a.student_id,a.student_name,

(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='math') as math,

(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='chinese') as chinese,

(select grade from a_a where student_id=a.student_id and student_name=a.student_name and subject='english') as english

from (SELECT student_id,student_name  FROM a_agroup by student_id,student_name) a