表tb1,字段有col1,col2,联合主键是(col1,col2),col1字段是类型,该字段只有有3个值,数据如下:
col1 col2 col3
1 a a1
1 b b1
2 a a2
2 c c2
3 a a3
3 b b3
3 d d3目标表tb2,主键是col2,要什么样的语句生成如下结果,也就是将表横过来:
col2 col3 col4 col5
a a1 a2 a3
b b1 0 a3
c 0 c2 0
d 0 0 d3------------------------
测试需要的临时表以及数据脚本如下:
drop table tb1;
drop table tb2;
create table tb1
(
col1 varchar2(5),
col2 varchar2(5),
col3 varchar2(5)
);
create table tb2
(
col2 varchar2(5),
col3 varchar2(5),
col4 varchar2(5),
col5 varchar2(5)
);
insert into tb1 values ('1','a','a1');
insert into tb1 values ('1','b','b1');
insert into tb1 values ('2','a','a2');
insert into tb1 values ('2','c','c2');
insert into tb1 values ('3','a','a3');
insert into tb1 values ('3','b','b3');
insert into tb1 values ('3','d','d3');
commit;
col1 col2 col3
1 a a1
1 b b1
2 a a2
2 c c2
3 a a3
3 b b3
3 d d3目标表tb2,主键是col2,要什么样的语句生成如下结果,也就是将表横过来:
col2 col3 col4 col5
a a1 a2 a3
b b1 0 a3
c 0 c2 0
d 0 0 d3------------------------
测试需要的临时表以及数据脚本如下:
drop table tb1;
drop table tb2;
create table tb1
(
col1 varchar2(5),
col2 varchar2(5),
col3 varchar2(5)
);
create table tb2
(
col2 varchar2(5),
col3 varchar2(5),
col4 varchar2(5),
col5 varchar2(5)
);
insert into tb1 values ('1','a','a1');
insert into tb1 values ('1','b','b1');
insert into tb1 values ('2','a','a2');
insert into tb1 values ('2','c','c2');
insert into tb1 values ('3','a','a3');
insert into tb1 values ('3','b','b3');
insert into tb1 values ('3','d','d3');
commit;
nvl(max(decode(col1,1,col3)),'0')col3,
nvl(max(decode(col1,2,col3)),'0')col4,
nvl(max(decode(col1,3,col3)),'0')col5
from tb1
group by col2
select col2,
nvl(max(decode(col1,1,col3)),'0')col3,
nvl(max(decode(col1,2,col3)),'0')col4,
nvl(max(decode(col1,3,col3)),'0')col5
from tb1
group by col2可以插入tb2表
2 select col2,decode(col1,1,col3,null) bak1,decode(col1,2,col3,null) bak2,decode(col1,3,col3,null) bak3 from tb1;表已创建。SQL> select * from tb2;未选定行SQL> desc tb2
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
COL2 VARCHAR2(5)
COL3 VARCHAR2(5)
COL4 VARCHAR2(5)
COL5 VARCHAR2(5)SQL> insert into tb2(col2) select distinct col2 from zzw_tab;已创建4行。SQL> select * from zzw_tab;COL2 BAK1 BAK2 BAK3
----- ----- ----- -----
a a1
b b1
a a2
c c2
a a3
b b3
d d3已选择7行。SQL> update tb2 a set a.col3=(select b.bak1 from zzw_tab b where a.col2=b.col2 and b.bak1 is not null);已更新4行。SQL> commit;提交完成。SQL> update tb2 a set a.col4=(select b.bak2 from zzw_tab b where a.col2=b.col2 and b.bak2 is not null);已更新4行。SQL> update tb2 a set a.col5=(select b.bak3 from zzw_tab b where a.col2=b.col2 and b.bak3 is not null);已更新4行。SQL> commit;提交完成。SQL> select * from tb2;COL2 COL3 COL4 COL5
----- ----- ----- -----
a a1 a2 a3
b b1 b3
c c2
d d3SQL> update tb2 set col3='0' where col3 is null;已更新2行。SQL> update tb2 set col4='0' where col4 is null;已更新2行。SQL> update tb2 set col5 ='0' where col5 is null;已更新 1 行。SQL> commit;提交完成。SQL> select * from tb2;COL2 COL3 COL4 COL5
----- ----- ----- -----
a a1 a2 a3
b b1 0 b3
c 0 c2 0
d 0 0 d3SQL>
select col2,
max(case when col1=1 then col3 else '0' end) as Col3,
max(case when col1=2 then col3 else '0' end) as Col4,
max(case when col1=3 then col3 else '0' end) as Col5
from tb1
group by col2
弄到
select col2,decode(col1,1,col3,0),decode(col1,2,col3,0),decode(col1,3,col3,0) from tb1;
无计了
select col2,
nvl(max(decode(col1,1,col3)),'0')col3,
nvl(max(decode(col1,2,col3)),'0')col4,
nvl(max(decode(col1,3,col3)),'0')col5
from tb1
group by col2 但是如果不是固定列数,那就必须通过动态语句,通过存储过程来实现。