declare str varchar2(100):=' '; lst varchar2(100); begin for ii in (select distinct f1 from test_ab) loop if str=' ' then str:='create table test_1 (sx varchar2(10) ,'||ii.f1||' varchar2(10))'; dbms_output.put_line(str); --execute immediate str; else str:='alter table test_1 add ('||ii.f1||' varchar(10))' ; dbms_output.put_line(str); --execute immediate str; end if; end loop;
for ii in (select distinct f2 from test_ab) loop str:='insert into test_1(sx) values('''||ii.f2||''')'; dbms_output.put_line(str); --execute immediate str; end loop;
commit;
for ii in (select distinct f2 from test_ab) loop for jj in (select f1 from test_ab where f2=ii.f2) loop str:='update test_1 set '||jj.f1||' = '''||ii.f2||''' where sx='''||ii.f2||''''; dbms_output.put_line(str); --execute immediate str; end loop; end loop; commit; end;生成的语句是: create table test_1 (sx varchar2(10) ,a varchar2(10)) alter table test_1 add (b varchar(10)) alter table test_1 add (c varchar(10)) insert into test_1(sx) values('s1') insert into test_1(sx) values('s2') insert into test_1(sx) values('s3') update test_1 set a = 's1' where sx='s1' update test_1 set b = 's1' where sx='s1' update test_1 set a = 's2' where sx='s2' update test_1 set c = 's2' where sx='s2' update test_1 set a = 's3' where sx='s3' update test_1 set b = 's3' where sx='s3'把注释的语名去了,就可以执行了,OK; SX A B C ---------- ---------- ---------- ---------- s1 s1 s1 s2 s2 s2 s3 s3 s3 满意吗?
tmprec.sql:=select distinct c2 from tab1;
while not rec.eof do
begin
tmpSql:=tmpSql+'decode(c2,' + tmprec.c2 + ',c3) ' + tmprec.c2 + '_value,';
tmprec.next
end;
rec.sql:='select ' + tmpSql + ' from rec
你们谁有最好的方案。写出来,我好给分。
就针对你自己的具体情况写,如果列数目固定且不多,可以写一条语句搞定,
如果要通用,不容易写。
我想可以传入表名,然后过程中从数据字典中获取该表的列名,循环里面拼接
动态sql串,如果你要在客户端执行,则返回该串,还可以把由该串得到的数据
插入表里面,客户端只需要select * 了
str varchar2(100):=' ';
lst varchar2(100);
begin
for ii in (select distinct f1 from test_ab) loop
if str=' ' then
str:='create table test_1 (sx varchar2(10) ,'||ii.f1||' varchar2(10))';
dbms_output.put_line(str);
--execute immediate str;
else
str:='alter table test_1 add ('||ii.f1||' varchar(10))' ;
dbms_output.put_line(str);
--execute immediate str;
end if;
end loop;
for ii in (select distinct f2 from test_ab) loop
str:='insert into test_1(sx) values('''||ii.f2||''')';
dbms_output.put_line(str);
--execute immediate str;
end loop;
commit;
for ii in (select distinct f2 from test_ab) loop
for jj in (select f1 from test_ab where f2=ii.f2) loop
str:='update test_1 set '||jj.f1||' = '''||ii.f2||''' where sx='''||ii.f2||'''';
dbms_output.put_line(str);
--execute immediate str;
end loop;
end loop;
commit;
end;生成的语句是:
create table test_1 (sx varchar2(10) ,a varchar2(10))
alter table test_1 add (b varchar(10))
alter table test_1 add (c varchar(10))
insert into test_1(sx) values('s1')
insert into test_1(sx) values('s2')
insert into test_1(sx) values('s3')
update test_1 set a = 's1' where sx='s1'
update test_1 set b = 's1' where sx='s1'
update test_1 set a = 's2' where sx='s2'
update test_1 set c = 's2' where sx='s2'
update test_1 set a = 's3' where sx='s3'
update test_1 set b = 's3' where sx='s3'把注释的语名去了,就可以执行了,OK;
SX A B C
---------- ---------- ---------- ----------
s1 s1 s1
s2 s2 s2
s3 s3 s3
满意吗?
但最好是这样了.我去看看Fenng(风) 的办法.
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)SQL*PLus> select job, deptno, count(*)
2 from emp
3 group by job, deptno;JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 2
CLERK 20 4
CLERK 30 2
ANALYST 20 4
MANAGER 10 2
MANAGER 20 2
MANAGER 30 2
SALESMAN 30 8
PRESIDENT 10 2已选择9行。SQL*PLus> select job,
2 max( decode( deptno, 10, cnt, null ) ) dept_10,
3 max( decode( deptno, 20, cnt, null ) ) dept_20,
4 max( decode( deptno, 30, cnt, null ) ) dept_30,
5 max( decode( deptno, 40, cnt, null ) ) dept_40
6 from ( select job, deptno, count(*) cnt
7 from emp
8 group by job, deptno )
9 group by job
10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40
--------- ---------- ---------- ---------- ----------
ANALYST 4
CLERK 2 4 2
MANAGER 2 2 2
PRESIDENT 2
SALESMAN 8SQL*PLus>
看贴主的情况而定。
列 ——>行,用数组 、动态sql
动态列理论上很容易实现。我觉得要真正在一个项目中灵活运用才是难点。
随便举个例子,比如说:一个人的工资项,有可能要增加,个人信息项也可能增加,系统的一些项也可能增加,但是他们的数据类型可能各不相同。能够有什么通用的类型来实现吗?ROW?
或者为每种数据类型都作成动态添加列表。