数据表的转置查询问题!!!
如下表
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;
/
--测试结果
1101423
221240
313220
