MySQL 行转列

假如有一个成绩表tom，如下

mysql> select * from tom;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| sam  | english |   100 |
| sam  | math    |   100 |
| sam  | chinese |    99 |
+------+---------+-------+
3 rows in set (0.00 sec)

需要在一行中显示各科成绩，以分步分析：
1.

mysql> select name,
    -> case subject when 'english' then score else 0 end  'english',
    -> case subject when 'math' then score else 0 end  'math',
    -> case subject when 'chinese' then score else 0 end  'chinese'
    -> from tom;
+------+---------+------+---------+
| name | english | math | chinese |
+------+---------+------+---------+
| sam  |     100 |    0 |       0 |
| sam  |       0 |  100 |       0 |
| sam  |       0 |    0 |      99 |
+------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select name,
    -> max(case subject when 'english' then score else 0 end)  'english',
    -> max(case subject when 'math' then score else 0 end)  'math',
    -> max(case subject when 'chinese' then score else 0 end)  'chinese'
    -> from tom group by name;
+------+---------+------+---------+
| name | english | math | chinese |
+------+---------+------+---------+
| sam  |     100 |  100 |      99 |
+------+---------+------+---------+
1 row in set (0.00 sec)