oracle 9i自动行转列,ORACLE 行转列 测试通过（9i）

--ORACLE 行转列  测试通过(9i)

/***************************************************************************************************

以学生成绩为例子，比较形象易懂

***********************************************************************************************************/

--1、行转列

--> --> 生成测试数据

drop table Class ;

Create table Class(Student varchar2(20),Course varchar2(20),Score number) ;

Insert into Class

(

select '张三','语文',78 from dual union all

select '张三','数学',87 from dual union all

select '张三','英语',82 from dual union all

select '张三','物理',90 from dual union all

select '李四','语文',65 from dual union all

select '李四','数学',77 from dual union all

select '李四','英语',65 from dual union all

select '李四','物理',85 from dual );

--case 方式:

select Student,sum(语文) 语文,sum(数学) 数学,sum(物理) 物理,sum(英语) 英语

from(

select  Student,

case Course when '语文' then Score else 0 end as 语文,

case Course when '数学' then Score else 0 end as 数学,

case Course when '物理' then Score else 0 end as 物理,

case Course when '英语' then Score else 0 end as 英语

from  Class )

group by Student;

--decode 方式:

select student,sum(decode(Course,'语文', Score,0)) as 语文,

sum(decode(Course,'数学', Score,0)) as 数学,

sum(decode(Course,'物理', Score,0)) as 物理,

sum(decode(Course,'英语', Score,0)) as 英语

from Class

group by student;

drop table Class ;