Oracle9i
有如下表 t
科室 项目名称 金额 项目序号
a X1 810 1
a X2 400 2
a X3 500 3
b X1 100 1
b X3 700 3
......我想得到如下结果集合科室 X1 X2 X3 ...
a 810 400 500 ...
b 500 700 ...
...........列数不定,列的顺序从左到右根据"项目序号"排序
请问我应该如何写sql?谢谢!
有如下表 t
科室 项目名称 金额 项目序号
a X1 810 1
a X2 400 2
a X3 500 3
b X1 100 1
b X3 700 3
......我想得到如下结果集合科室 X1 X2 X3 ...
a 810 400 500 ...
b 500 700 ...
...........列数不定,列的顺序从左到右根据"项目序号"排序
请问我应该如何写sql?谢谢!
--测试数据
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
存储过程拼sql了..
例子如下:
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下:
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student