--测试: create table TABLE1(memname varchar(10), PROJECT varchar(10),manhour int);insert into table1 values( '人员1','工程1',23); insert into table1 values( '人1','工程2',10); insert into table1 values( '人2','工程1',10); insert into table1 values( '人3','工程2',15); insert into table1 values( '人4','工程3',50);/* 写了个大概,动态SQL语句构造成功了,不过我还不太会执行它,好象要用到包才行的,暂时不太会。 */declare type proTab is table of table1.project%type; v_project proTab; v_sql varchar2(4000); cursor cur is select distinct project from table1; begin v_sql := 'select memname'; open cur; fetch cur bulk collect into v_project; close cur; for i in 1..v_project.count loop v_sql := v_sql || ',sum(case PROJECT when '''||v_project(i)||''' then manhour else 0 end) '||v_project(i)||''; end loop; v_sql := v_sql || ',sum(manhour) as 合计 from table1 group by memname'; dbms_output.put_line(v_sql); -- execute immediate v_sql; end;/*执行结果为: select memname,sum(case PROJECT when '工程1' then manhour else 0 end) 工程1,sum(case PROJECT when '工程2' then manhour else 0 end) 工程2,sum(case PROJECT when '工程3' then manhour else 0 end) 工程3,sum(manhour) as 合计 from table1 group by memname *//* 当然如果直接把上面的运行结果复制过来执行的话,结果即为所求: MEMNAME 工程1 工程2 工程3 合计 ---------- ---------- ---------- ---------- ---------- 人1 0 10 0 10 人2 10 0 0 10 人3 0 15 0 15 人4 0 0 50 50 人员1 23 0 0 23已选择5行。 */
CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_rc OUT myrctype); END pkg_test; CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get ( p_rc OUT myrctype) ISbegin declare type proTab is table of table1.project%type; v_project proTab; v_sql varchar2(4000); cursor cur is select distinct project from table1; begin v_sql := 'select memname'; open cur; fetch cur bulk collect into v_project; close cur; for i in 1..v_project.count loop v_sql := v_sql || ',sum(case PROJECT when '''||v_project(i)||''' then manhour else 0 end) '||v_project(i)||''; end loop; v_sql := v_sql || ',sum(manhour) as 合计 from table1 group by memname'; -- dbms_output.put_line(v_sql); -- execute immediate v_sql; open p_rc for v_sql; end; end;end;
create table TABLE1(memname varchar(10), PROJECT varchar(10),manhour int);insert into table1 values( '人员1','工程1',23);
insert into table1 values( '人1','工程2',10);
insert into table1 values( '人2','工程1',10);
insert into table1 values( '人3','工程2',15);
insert into table1 values( '人4','工程3',50);/*
写了个大概,动态SQL语句构造成功了,不过我还不太会执行它,好象要用到包才行的,暂时不太会。
*/declare
type proTab is table of table1.project%type;
v_project proTab;
v_sql varchar2(4000);
cursor cur is select distinct project from table1;
begin
v_sql := 'select memname';
open cur;
fetch cur bulk collect into v_project;
close cur;
for i in 1..v_project.count loop
v_sql := v_sql || ',sum(case PROJECT when '''||v_project(i)||''' then manhour else 0 end) '||v_project(i)||'';
end loop;
v_sql := v_sql || ',sum(manhour) as 合计 from table1 group by memname';
dbms_output.put_line(v_sql);
-- execute immediate v_sql;
end;/*执行结果为:
select memname,sum(case PROJECT when '工程1' then manhour else 0 end) 工程1,sum(case PROJECT when '工程2' then manhour else 0 end) 工程2,sum(case PROJECT when '工程3' then manhour else 0 end) 工程3,sum(manhour) as 合计 from table1 group by memname
*//*
当然如果直接把上面的运行结果复制过来执行的话,结果即为所求:
MEMNAME 工程1 工程2 工程3 合计
---------- ---------- ---------- ---------- ----------
人1 0 10 0 10
人2 10 0 0 10
人3 0 15 0 15
人4 0 0 50 50
人员1 23 0 0 23已选择5行。
*/
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_rc OUT myrctype);
END pkg_test;
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get ( p_rc OUT myrctype)
ISbegin
declare
type proTab is table of table1.project%type;
v_project proTab;
v_sql varchar2(4000);
cursor cur is select distinct project from table1;
begin
v_sql := 'select memname';
open cur;
fetch cur bulk collect into v_project;
close cur;
for i in 1..v_project.count loop
v_sql := v_sql || ',sum(case PROJECT when '''||v_project(i)||''' then manhour else 0 end) '||v_project(i)||'';
end loop;
v_sql := v_sql || ',sum(manhour) as 合计 from table1 group by memname';
-- dbms_output.put_line(v_sql);
-- execute immediate v_sql;
open p_rc for v_sql;
end;
end;end;
oracle 如何做行列动态的交叉表