竖表转横表的SQL语句？
如何用最简单的SQL(最好一句话就可以实现为好)完成如下功能：假设有表：Score
//=================
pname bname score
A Chinese 80.0
A Math 70.0
A EngLish 80.0
B Computer 120.0
要求实现功能：
显示结果为：
pname bname chinesescore bname mathscore bname englishscore
A chinese 80 math 70 english 80 select pname,bname,decode(bname,'chinese',score) as
chinesescore ,decode(bname,'Math',score) as
Mathscore,decode(bname,'EngLish',score) as
EngLishscore ,decode(bname,'Computer' ,score) as
Computer score from tablename 先写个函数(作用：把学科和分数并起来)
create or replace function f_get_bname_score(var_a1 in varchar2) return varchar2 is
v_Result varchar2(32767);
cursor c_bname is
select bname,score
from 表名
where pname = var_a1;
begin
for cbname in c_bname loop
v_Result := v_Result ||cbname.bname||'~'||cbname.score ||'/';
end loop;
return(v_Result);
end f_get_bname_score;
查询(反复利用Oracle instr和substr函数):
select pname,
substr(ChineseScore, 1, instr(ChineseScore, '~') - 1) as bname,
substr(ChineseScore, instr(ChineseScore, '~') + 1, 20) as ChineseScore,
substr(EnglishScore, 1, instr(EnglishScore, '~') - 1) as bname,
substr(EnglishScore, instr(EnglishScore, '~') + 1, 20) as EnglishScore,
substr(MathScore, 1, instr(MathScore, '~') - 1) as bname,
substr(MathScore, instr(MathScore, '~') + 1, 20) as MathScore
from (select pname,
--rtrim(a, '/'),
substr(a, 1, instr(a, '/') - 1) as ChineseScore,
rtrim(substr(a, - (instr(a, '/'))), '/') as EnglishScore,
--substr(a,instr(a, '/')+1),
substr(substr(a, instr(a, '/') + 1),
1,
instr(substr(a, instr(a, '/') + 1), '/') - 1) as MathScore
from (select distinct pname, f_get_bname_score(pname) as a
from test_table))
缺点：
学科是固定3个的，若多于3个需要修改函数和修改查询语句 create table test_table
(
pname varchar2(100),
bname varchar2(100),
score varchar2(100)
);
insert into test_table values('A','Chinese','80.0');
insert into test_table values('A','Math','70.0');
insert into test_table values('A','EngLish','80.0');
insert into test_table values('B','Computer','120.0');
commit;
select pname,max(decode(cn,1,bname)) bname,max(decode(cn,1,score)) ChineseScore,
max(decode(cn,2,bname)) bname,max(decode(cn,2,score)) EnglishScore,
max(decode(cn,3,bname)) bname,max(decode(cn,3,score)) MathScore
from (select pname,bname,score,row_number() over(partition by pname order by bname) cn from test_table) a
group by pname;
页:
[1]