我有一个表 有两个列
datetime num
200801 8
200802 4
200803 3
200804 6
200805 3
200806 2
200807 7
200808 8
200809 2
200810 1
200811 0
.....有n行,
现在要求转换为
有n列,一行,
就是
datetime1 datetime2 datetime3 datetime4 datetime5 datetime6 ....
200801 200802 200803 200803 200803 200803 ....
请求一个存储过程 谢谢
datetime num
200801 8
200802 4
200803 3
200804 6
200805 3
200806 2
200807 7
200808 8
200809 2
200810 1
200811 0
.....有n行,
现在要求转换为
有n列,一行,
就是
datetime1 datetime2 datetime3 datetime4 datetime5 datetime6 ....
200801 200802 200803 200803 200803 200803 ....
请求一个存储过程 谢谢
解决方案 »
- 怎么用toad找出最耗资源的sql呢?(Oracle)
- 超难问题:有没有好办法解决大量数据录入的问题
- 以SYSTEM身份登录,login_user值为SYS ???
- oracle 9.2.0.1 升级到9.2.0.8 后EXP IMP出错
- 日期类型在windows中和在Linux中有区别吗
- oracle 关于union统计查询
- unix下备份的oracle可否直接在windows下恢复
- 关于自动递增字段
- 请大家帮我看oracle中触发器验证不通过:ora-04098
- 连接数据库的时候,报Oracle not avaliabe!!??
- [高分]我在oracle上建的用户,为什么在本地可以登录,在远程却报无效的用户名、密码错误?谢谢
- toad for oracle 和 pl/sql 哪个用的比较多,哪个比较好呢?
max(case when datetime='200801' then datetime else '' end) as datetime1,
max(case when datetime='200802' then datetime else '' end) as datetime2
from(
select '200801' as datetime,8 as num from dual
union
select '200802',4 from dual)
/*
DATETIME1 DATETIME2
200801 200802
*/
sum(decode(datetime,'200802',datetime)) as datetime2
from A
create table A
(
datetime varchar(20),
num varchar(10)
)
insert into A values('200801',8);
insert into A values('200802',4);
insert into A values('200803',3);
--写一个过程
declare
v_datetime varchar(20);
v_i int:=0;
v_s varchar(1000);
v_sql varchar(2000);
cursor c
is
select datetime from A;
begin
v_s := 'select ';
open c;
loop
fetch c into v_datetime;
if c%found then
v_i :=v_i+1;
v_s := v_s||'sum(decode(datetime,'||v_datetime||',datetime)) as datetime'||v_i||',' ;
else
exit;
end if;
end loop;
v_s := SUBSTR(v_s, 0, LENGTH(v_s) - 1);
v_sql := v_s||' from A';
dbms_output.put_line('v_sql='||v_sql);
end;
打印出的sql为:
v_sql=select sum(decode(datetime,200801,datetime)) as
datetime1,sum(decode(datetime,200802,datetime)) as
datetime2,sum(decode(datetime,200803,datetime)) as datetime3 from A
你写过程的时候做个输出参数,把这个v_sql返回就可以了
str1 varchar2(4000);
str2 varchar2(4000);
temp number;
l_c number;
cursor s1 is select id from rpt;
begin
str1:='';
l_c:=1;
open s1;
loop
fetch s1 into temp;
if s1%found then
str1:=str1||to_char(temp)||' d'||to_char(l_c)||',';
l_c:=l_c+1;
else
exit;
end if;
end loop;
close s1;
str1:=substr(str1,0,length(str1)-1);
str2:='select '||str1||' from dual';
dbms_output.put_line(str2);
end l2c;
/SQL> select * from rpt; ID STATUS
---------- ----------
1 1
2 1
3 1
4 2
5 2
6 1
7 1
8 1已选择8行。SQL> exec l2c;
select 1 d1,2 d2,3 d3,4 d4,5 d5,6 d6,7 d7,8 d8 from dualPL/SQL 过程已成功完成。SQL> select 1 d1,2 d2,3 d3,4 d4,5 d5,6 d6,7 d7,8 d8 from dual; D1 D2 D3 D4 D5 D6 D7 D8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 5 6 7 8