如下表
XH DDATE SXF
------- -------------- ----------
1 01-1月 -07 10
1 02-1月 -07 14
1 03-1月 -07 23
2 02-1月 -07 21
2 03-1月 -07 24
3 01-1月 -07 13
3 02-1月 -07 22
......
想做成如下表XH 070101 070102 070103 ...........
1 10 14 23
2 0 21 24
3 13 22 0
Oracle中该如何实现
谢谢各位大大
XH DDATE SXF
------- -------------- ----------
1 01-1月 -07 10
1 02-1月 -07 14
1 03-1月 -07 23
2 02-1月 -07 21
2 03-1月 -07 24
3 01-1月 -07 13
3 02-1月 -07 22
......
想做成如下表XH 070101 070102 070103 ...........
1 10 14 23
2 0 21 24
3 13 22 0
Oracle中该如何实现
谢谢各位大大
--测试数据
create table t (XH varchar2(10), DDATE date, SXF int);
insert into t
select 1,sysdate,10 from dual union all
select 1,sysdate+1,14 from dual union all
select 1,sysdate+2,23 from dual union all
select 2,sysdate,21 from dual union all
select 2,sysdate+1,24 from dual union all
select 3,sysdate,13 from dual union all
select 3,sysdate+1,22 from dual;
--
create or replace package sp_test
is
type ResultData is ref cursor;
procedure getRstData( rst out ResultData);
end sp_test;
/
create or replace package body sp_test
is
procedure getRstData( rst out ResultData)
is
begin
declare
cursor cur is select distinct (DDATE) from t;
tmp_ddate date;
str varchar2(4000);
begin
str:='select xh';
open cur;
loop
fetch cur into tmp_ddate;
exit when cur%notfound;
str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"';
end loop;
str:=str||' from t group by xh';
-- dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
end sp_test;
/--测试结果
1 10 14 23
2 21 24 0
3 13 22 0
PLS-00221: 'SP_TEST' 不是过程或尚未定义
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored不对呀 我在Oracle10g中 执行报错呀?
select xh,
sum(decode(to_char(ddate,'yyyymmdd'),'20070726',sxf,0)) "20070726",
sum(decode(to_char(ddate,'yyyymmdd'),'20070727',sxf,0)) "20070727",
sum(decode(to_char(ddate,'yyyymmdd'),'20070728',sxf,0)) "20070728"
from t group by xh
这样的一个sql
BEGIN sp_test.getRstData(t); END; *
第 1 行出现错误:
ORA-06550: 第 1 行, 第 26 列:
PLS-00357: 在此上下文中不允许表, 视图或序列引用 'T'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored还是不行嘛!