oracle+行换列,Oracle_行列互转

Oracle: 10g, SQL PLUS

行转列

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

NameSubjectResult

JackEnglish90

JackChinese91

JackMath92

FredEnglish90

FredChinese91

FredMath92

想转换成

NameEnglishChineseMath

Jack909192

Fred808182

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):

NameEnglishChineseMath

Jack909192

Fred808182

转换成：

NameSubjectResult

JackEnglish90

JackChinese91

JackMath92

FredEnglish90

FredChinese91

FredMath92

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: