跪求大神帮忙，解决一个难题

[williamwilliam]

业务要求：把A表内的数据统计整理到B表中，根据A表内 a1 字段的值（设为x） 将该条数据的a2 填入B表内字段为x的列中。


A表
id a1 a2
1 b1 **
1 b2 *
2 b1 hh

B 表
id b1 b2
1 ** *
2 hh





自己写的存储过程如下:
CREATE OR REPLACE PROCEDURE DataTrans is
cursor mycur is select * from insurance where primaryconditiongroup !=' ';
id varchar(16);
mem varchar(16);
style number;
time varchar2(160);
total varchar2(320);
field varchar2(16);
oldtime varchar2(160);

BEGIN
for oneData in mycur
loop
id := oneData.memberid;
time := oneData.lengthofstay;
if time is null then
time:=' ';
end if;
field := oneData.PRIMARYCONDITIONGROUP;
execute immediate 'select member, '||field||' from insurance2 where member = '||id;
if mem is null then
total := time;
execute immediate 'insert into insurance2(member,'||field||',totallength) values(55,55,44)';
else
time := time||oldtime;
total := total||time;
execute immediate 'update insurance2 set('||field||',totallength) = ('||time||','||total||') where member = '||id;
end if;
end loop;
END;
/

[啊彪123]

动态行列转换，自己找资料。没人会帮忙把代码完全写好！

[ssqtjffcu]

create table A(
ID INT,
A1 VARCHAR2(10),
A2 VARCHAR2(10)
);
INSERT INTO A VALUES (1,'b1','**');
INSERT INTO A VALUES (1,'b2','*');
INSERT INTO A VALUES (2,'b1','hh');
COMMIT;
CREATE TABLE B AS
SELECT ID, MAX(DECODE(A1, 'b1', A2)), MAX(DECODE(A1, 'b2', A2))
  FROM A
 GROUP BY ID;
SELECT * FROM B;

 
                                     ID B1         B2
--------------------------------------- ---------- ----------
                                      1 **         *
                                      2 hh         

[ssqtjffcu]

create table A(
ID INT,
A1 VARCHAR2(10),
A2 VARCHAR2(10)
);
INSERT INTO A VALUES (1,'b1','**');
INSERT INTO A VALUES (1,'b2','*');
INSERT INTO A VALUES (2,'b1','hh');
COMMIT;
CREATE TABLE B AS
SELECT ID, MAX(DECODE(A1, 'b1', A2)), MAX(DECODE(A1, 'b2', A2))
  FROM A
 GROUP BY ID;
SELECT * FROM B;

 
                                     ID B1         B2
--------------------------------------- ---------- ----------
                                      1 **         *
                                      2 hh