我上课用的案例,不知对你有没有帮助! -----martixwang in 北大青鸟 ------------------------------------------------------------ 行转列案例: 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;
你的第二张表中的 YZF 数据是从哪来的啊?没有的话怎么实现
acct_item_字段一对多:select 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
------------------------------------------------------------
行转列案例:
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