Oracle_行列互转

Oracle: 10g, SQL PLUS

 

行转列

假设有张学生成绩表（rl_test)如下：

Name                   Subject                               Result

Jack                      English                               90

Jack                      Chinese                              91

Jack                      Math                                    92

Fred                      English                               90

Fred                      Chinese                              91

Fred                      Math                                    92

 

想转换成

Name                   English                               Chinese                              Math

Jack                      90                                          91                                           92

Fred                      80                                          81                                           82

 

Code：

 

create table rl_test

(Name varchar2(30),

Subject varchar2(30),

Result int);

 

begin

insert into rl_test values ('Jack', 'English', 90);

insert into rl_test values ('Jack', 'Chinese', 91);

insert into rl_test values ('Jack', 'Math', 92);

insert into rl_test values ('Fred', 'English', 80);

insert into rl_test values ('Fred', 'Chinese', 81);

insert into rl_test values ('Fred', 'Math', 82);

commit;

end;

 

Screen shot:

 

 

 

－－静态sql， 指定Subject只有'English', 'Chinese', 'Math'这三门课程。

select name,

    max(case subject when 'English' then Result else 0 end) as English,

    max(case subject when 'Chinese' then Result else 0 end) as Chinese,

    max(case subject when 'Math' then Result else 0 end) as Math

from rl_test

group by name order by name desc;

 

Screen shot:

 

*加个平均分和总分

select name,

    max(case subject when 'English' then Result else 0 end) as English,

    max(case subject when 'Chinese' then Result else 0 end) as Chinese,

    max(case subject when 'Math' then Result else 0 end) as Math,

    cast(avg(result*1.0) as decimal(18,2)) Average,

    sum(result) as SUM

from rl_test

group by name order by name desc;

 

－－动态sql， 指定 Subject不止'English', 'Chinese', 'Math'这三门课程。

Mysql:

declare @sql varchar(8000)

set @sql = 'select Name as ' + '姓名'

select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'

from (select distinct Subject from tb) as a

set @sql = @sql + ' from tb group by name'

exec(@sql)

 

Oracle:

create or replace procedure row_to_col is

Dy_sql varchar2(4000);

cursor cur_subject is select distinct subject from rl_test;

begin

Dy_sql := 'select name ';

for cur in cur_subject loop

Dy_sql := Dy_sql || ' , max(case subject when ''' || cur.subject || ''' then result else 0 end) as ' || '' ||cur.subject ||'';

end loop;

Dy_sql := Dy_sql || ' from rl_test group by name';

Dy_sql := 'create or replace view rl_result_vw as ' || Dy_sql;

dbms_output.put_line(Dy_sql);

execute immediate Dy_sql;

end;

 

begin

row_to_col;

end;

 

select * from rl_result_vw;

 

Screen shot:

 

注：若在执行procedure是遇到"权限不足"问题， 解决办法是赋予用户Create Any Table的权限:

 GRANT CREATE ANY TABLE TO SCOTT

 

列转行:

将table(rl_test2):

Name                   English                               Chinese                              Math

Jack                      90                                          91                                           92

Fred                      80                                          81                                           82

转换成：

Name                   Subject                               Result

Jack                      English                               90

Jack                      Chinese                              91

Jack                      Math                                    92

Fred                      English                               90

Fred                      Chinese                              91

Fred                      Math                                    92

 

Code：

create table rl_test2 as select * from rl_result_vw;

 

Screen shot:

 

select * from

(

select name, 'Chinese' as subject, Chinese as result from rl_test2

union all

select name, 'English' as subject, English as result from rl_test2

union all

select name, 'Math' as subject, Math as result from rl_test2

)

order by name desc, case subject when 'Chinese' then 1 when 'English' then 2 when 'Math' then 3 end;

 

Screen shot:

1.jpg

2.jpg

3.jpg

4.jpg

5.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24025515/viewspace-718736/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24025515/viewspace-718736/