oracle 10g 行列转换 通过decode和union all 实现

行转列例子：  

select * from grade  

 

 

 

 

 

select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文,
max(decode(subject,'ma',grade,null)) 数学 from grade group by student 

列转行：

创建测试表

create table test as
select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文,
max(decode(subject,'ma',grade,null)) 数学 from grade group by student

select * from test

 

 

select student,'英语' 科目,英语 from test
union all
select student,'语文' 科目,语文 from test
union all
select student,'数学' 科目,数学 from test