SELECT 纵向编号, SUM(DECODE(横向编号,'A', 交叉点的值)) AS A , SUM(DECODE(横向编号,'B', 交叉点的值)) AS B FROM T GROUP BY 纵向编号
行数不确定的不好直接用sql来实现.
create table AA ( HJD VARCHAR2(10), SJD VARCHAR2(10), JCD NUMBER ); insert into AA (HJD, SJD, JCD) values ('A', 'D', 1); insert into AA (HJD, SJD, JCD) values ('B', 'D', 2); insert into AA (HJD, SJD, JCD) values ('C', 'D', 3); insert into AA (HJD, SJD, JCD) values ('A', 'E', 4); insert into AA (HJD, SJD, JCD) values ('B', 'E', 5.1); insert into AA (HJD, SJD, JCD) values ('C', 'E', 6.2); insert into AA (HJD, SJD, JCD) values ('A', 'F', .6); insert into AA (HJD, SJD, JCD) values ('B', 'F', .8); insert into AA (HJD, SJD, JCD) values ('C', 'F', .3); commit; 创建存储过程返回动态组合的sql; create or replace procedure P_ATEST( as_sql out varchar2 )is type v_cursor is ref cursor; v_cur v_cursor; v_hjd varchar2(100); v_str varchar2(300); v_spa varchar2(10); v_sgn varchar2(10); v_sql varchar2(40); v_tmp varchar2(4000); begin v_sql := 'select sjd '; v_str :=''; v_spa := ' '; v_sgn := ','; open v_cur for select distinct HJD from AA; loop fetch v_cur into v_hjd; exit when v_cur%notfound; v_str := v_str||v_sgn||'sum(decode(hjd,'''||v_hjd||''',jcd)) as '||v_hjd||' '; end loop; v_tmp := v_sql||v_str||' FROM aa GROUP BY sjd'; as_sql :=v_tmp; close v_cur;
纵向编号,
SUM(DECODE(横向编号,'A', 交叉点的值)) AS A ,
SUM(DECODE(横向编号,'B', 交叉点的值)) AS B
FROM T GROUP BY 纵向编号
(
HJD VARCHAR2(10),
SJD VARCHAR2(10),
JCD NUMBER
);
insert into AA (HJD, SJD, JCD)
values ('A', 'D', 1);
insert into AA (HJD, SJD, JCD)
values ('B', 'D', 2);
insert into AA (HJD, SJD, JCD)
values ('C', 'D', 3);
insert into AA (HJD, SJD, JCD)
values ('A', 'E', 4);
insert into AA (HJD, SJD, JCD)
values ('B', 'E', 5.1);
insert into AA (HJD, SJD, JCD)
values ('C', 'E', 6.2);
insert into AA (HJD, SJD, JCD)
values ('A', 'F', .6);
insert into AA (HJD, SJD, JCD)
values ('B', 'F', .8);
insert into AA (HJD, SJD, JCD)
values ('C', 'F', .3);
commit;
创建存储过程返回动态组合的sql;
create or replace procedure P_ATEST(
as_sql out varchar2
)is
type v_cursor is ref cursor;
v_cur v_cursor;
v_hjd varchar2(100);
v_str varchar2(300);
v_spa varchar2(10);
v_sgn varchar2(10);
v_sql varchar2(40);
v_tmp varchar2(4000);
begin
v_sql := 'select sjd ';
v_str :='';
v_spa := ' ';
v_sgn := ',';
open v_cur for
select distinct HJD from AA;
loop
fetch v_cur into v_hjd;
exit when v_cur%notfound;
v_str := v_str||v_sgn||'sum(decode(hjd,'''||v_hjd||''',jcd)) as '||v_hjd||' ';
end loop;
v_tmp := v_sql||v_str||' FROM aa GROUP BY sjd';
as_sql :=v_tmp;
close v_cur;
end P_ATEST;