sql中行列转换

之前在赛门铁克笔试时遇到一道SQL的数据库查询题，经过自己分析和寻求帮助解决了，现在分享下:
1.表score(ID,Name,Course,Score)
数据：
ID    Name       Course     Score
0     wang	English	    99
0     wang	Chinese	    90
0     wang	Math 	    90
1	pp	Chinese	    99
1	pp	Math	    99
1	pp	English	    88

 表student(ID,Name,Chinese,Math,English)
数据：
ID   Name    Chinese  Math  English
0    wang    90       90    99
1    pp      99       99    88

 

2.由表score获得表student数据（行->列）

对于MySQL来说：
select ID,
    name,
    sum(case
        when course = 'Chinese' then score
    end) 'Chinese',
    sum(case
        when course = 'Math' then score
    end) 'Math',
    sum(case
        when course = 'English' then score
    end) 'English'
from
    score
group by ID,Name;
但是对于Oracle来说，可以有decode函数实现：
select ID,
    Name,
    sum(decode(Course, 'Chinese', Score, null)) as Chinese,
    sum(decode(Course, 'Math', Score, null)) as Math,
    sum(decode(Course, 'English', Score, null)) as English
from
    score
group by ID,Name;

3.由表student获取score查询结果(列->行)

select id,name,'Chinese' course,chinese score from student
union
select id,name,'Math' course,math score from student
union
select id,name,'English' course,english score from student
order by id;

以上学习参考了别人的博客，只希望自己也总结下，记忆深刻点。