oralce行转列

1.创建表

Create Table temp 
(
num varchar2(15 Char),
name varchar2(20 Char),
sex varchar2(2 Char),
classes varchar2(30 Char),
course_name varchar2(50 Char)
)

2.插入数据

insert  into    temp(num,name,sex,classes,course_name) values ('206211','王艺','男','06-1班','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务管理');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务会计');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','电子商务');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公共经济学');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公司理财');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','管理学原理');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务管理');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务会计');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','电子商务');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','公共经济学');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','环境管理学');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','管理学原理');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','商务谈判');
insert into temp(num,name,sex,classes,course_name) values ('206216','李佳琪','男','06-2','土地估计');
Commit;

3.查询

select num, name, sex, classes,
       max(decode(rn, 1, course_name, null)) course_name_1,
       max(decode(rn, 2, course_name, null)) course_name_2,
       max(decode(rn, 3, course_name, null)) course_name_3,
       max(decode(rn, 4, course_name, null)) course_name_4,
       max(decode(rn, 5, course_name, null)) course_name_5
from (select num, name, sex, classes, course_name,
      row_number() over(partition by num, name, sex, classes order by course_name) rn from temp)
group by num, name, sex, classes;

4.结果

[img]http://dl.iteye.com/upload/attachment/199266/c806ebad-25fe-3559-b7ae-6b747c6bc492.jpg[/img]