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_a group by student_id,student_name) a