--测试
declare col1 dic_table.dic_name%type; --定义类型
sqlstr varchar2(8000);
cursor c_try is select distinct dic_name from dic_table;
begin
sqlstr:='';
open c_try;
loop
fetch c_try into col1;
exit when c_try%notfound;
sqlstr:=sqlstr||', sum(decode(dic_name,'''||col1||''',dic_value,0)) as '||col1;
exit when c_try%notfound;
end loop;
close c_try;
sqlstr:='create table tt as select '||substr(sqlstr,2)||' from dic_table';
--tt为刚创建的一个保存结果的表
execute immediate sqlstr;
end;--执行
select * from tt;
declare col1 dic_table.dic_name%type; --定义类型
sqlstr varchar2(8000);
cursor c_try is select distinct dic_name from dic_table;
begin
sqlstr:='';
open c_try;
loop
fetch c_try into col1;
exit when c_try%notfound;
sqlstr:=sqlstr||', sum(decode(dic_name,'''||col1||''',dic_value,0)) as '||col1;
exit when c_try%notfound;
end loop;
close c_try;
sqlstr:='create table tt as select '||substr(sqlstr,2)||' from dic_table';
--tt为刚创建的一个保存结果的表
execute immediate sqlstr;
end;--执行
select * from tt;
11:13:46 SQL> desc dic_table;
名称 空? 类型
----------------------------------------- -------- ---------------------------- DIC_NAME CHAR(1)
DIC_VALUE NUMBER(38)11:13:50 SQL> select * from dic_table;
a 1
b 2
c 2
d 3已用时间: 00: 00: 00.00
11:13:56 SQL> declare
11:14:01 2 type arr_dic_name is varray(100) of varchar2(10);
11:14:01 3 type arr_dic_value is varray(100) of varchar2(10);
11:14:01 4 arr_dic_name1 arr_dic_name:=arr_dic_name();
11:14:01 5 arr_dic_value1 arr_dic_value:=arr_dic_value();
11:14:01 6 cursor cur_dic_name is select dic_name,dic_value from dic_table;
11:14:01 7 col_count integer;
11:14:01 8 i integer;
11:14:01 9 begin
11:14:01 10 select count(dic_name) into col_count from dic_table;
11:14:01 11 i:=1;
11:14:01 12 for cur1 in cur_dic_name loop
11:14:01 13 arr_dic_name1.extend;
11:14:01 14 arr_dic_name1(i):=cur1.dic_name;
11:14:01 15 arr_dic_value1.extend;
11:14:01 16 arr_dic_value1(i):=cur1.dic_value;
11:14:01 17 i:=i+1;
11:14:01 18 end loop;
11:14:01 19
11:14:01 20 for i in 1..col_count loop
11:14:01 21 if i=col_count then
11:14:01 22 dbms_output.put_line(lpad(arr_dic_name1(i),5,' '));
11:14:01 23 else
11:14:01 24 dbms_output.put(lpad(arr_dic_name1(i),5,' '));
11:14:01 25 end if;
11:14:01 26 end loop;
11:14:01 27
11:14:01 28 for i in 1..col_count loop
11:14:01 29 if i=col_count then
11:14:01 30 dbms_output.put_line(lpad(arr_dic_value1(i),5,' '));
11:14:01 31 else
11:14:01 32 dbms_output.put(lpad(arr_dic_value1(i),5,' '));
11:14:01 33 end if;
11:14:01 34 end loop;
11:14:01 35 end;
11:14:01 36 /
a b c d
1 2 2 3PL/SQL 过程已成功完成。已用时间: 00: 00: 00.00
有几个列需要几个,这个针对相对固定的比较好,如果值是不确定的话,就需要写个过程来解决了!