天呀ORACLE版尽然没有人会呀!
解决方案 »
- suse10安装oracle11g问题,求高手指导。
- 基表发生改变物化视图不能拿正常使用
- 几个ORACLE问题请帮忙!!!
- 菜鸟问题,谢谢
- 集群连接池的配置
- 執行超長的SQL語句報錯:ORA-00600:內部錯誤代碼,參數:[733],[33803732],[top call heap],[],[],[],[],[]
- 小弟不才,求一条sql语句 !!!!!!!!!!!!!!!!!!!!!!!!!
- 各位大侠,请支持一把!!!!!!!!!!!!!!!!!!!!!!!!!!
- 大家谁有ORACLE Developer/2000 的开发指南或者教学之类的电子书?200分
- 急:有关management server服务无法启动的问题?
- oracle在windows2000下?
- 如何用like实现如windows操作系统中?的查询效应
------------------------------------------------------------
行转列案例:
1\求出所有的科目.
2\根据所有的科目动态生成一个CREATE 语句.此CREATE 语句为建立横着
的成绩的表命令.
3\通过动态SQL执行生成的SQL语句
4\求出所有的学生.
5\求每一门学生的每一门科目,依次填入横着的成绩表
create table score
(
xh varchar(4),
subject varchar(10),
score number(6,2) default 0
)
declare
cursor kmcur is select distinct subject from score;
cursor stucur is select distinct xh from score;
cursor scorecur(yourxh varchar) is select score from score where xh=yourxh;
mykm varchar(10):='';
myxh varchar(4):='';
createsql varchar(200):='';
vcursor int:=0;
myscore int:=0;
scorelist varchar(100):='';
insertstr varchar(200):='';
fineinsertstr varchar(200):='';
subjectlist varchar(100):='';
begin
insertstr:='insert into newscore (xh';
createsql:='create table newscore (xh varchar(4)';
open kmcur;
loop
fetch kmcur into mykm;
exit when kmcur%notfound;
createsql:=createsql||','||mykm||' '||'number(6,2)';
subjectlist:=subjectlist||','||mykm;
end loop;
close kmcur;
createsql:=createsql||')';
-- dbms_output.put_line(subjectlist);
insertstr:=insertstr||subjectlist||') values (';
-- dbms_output.put_line(insertstr);
vcursor:=dbms_sql.open_cursor;
dbms_sql.parse(vcursor,createsql,dbms_sql.native);
dbms_sql.close_cursor(vcursor);
open stucur;
loop
fetch stucur into myxh;
exit when stucur%notfound;
fineinsertstr:=insertstr||myxh||',';
open scorecur(myxh);
loop
fetch scorecur into myscore;
exit when scorecur%notfound;
-- dbms_output.put_line(myxh||' '||myscore);
scorelist:=scorelist||trim(to_char(myscore))||',';
end loop;
scorelist:=substr(scorelist,1,length(scorelist)-1);
execute immediate fineinsertstr||scorelist||')';
fineinsertstr:='';
scorelist:='';
close scorecur;
end loop;
close stucur;
end;
a.acc_nbr,
sum(decode(b.bz,'YZF',a.charge,0)) YZF,
sum(decode(b.bz,'SJF',a.charge,0)) SJF
from
table1 a,
table2 b
where
a.acct_item_=b.acct_item_
group by
a.acc_nbr