1.建表
复制代码 代码如下:
-- Create table
create table test
(
dm1 char(3),
dm2 char(3),
mc1 nvarchar2(20),
val nvarchar2(20)
)
2.填入数据如下:
DM1 DM2 MC1 VAL
101 1 c1 100
101 1 c2 80
101 1 c3 40
101 2 c1 30
101 2 c2 80
102 4 c1 9
102 6 c2 50
转换后数据显示如下:
DM1 DM2 c1 c2 c3
101 1 100 80 40
101 2 30 80
102 4 9
102 6 50
3.转换语句:
复制代码 代码如下:
select dm1,dm2,sum(decode(mc1,'c1',val)) c1,sum(decode(mc1,'c2',val)) c2,sum(decode(mc1,'c3',val)) c3
from test
group by dm1,dm2
注:该语句是在val为数值的情况下的执行方法,如果val不为数值型,或者包含字符串等类型的话,运用sum会报错,这里可以采用另外一个方法,将sum改为max即可,语句如下:
复制代码 代码如下:
select dm1,dm2,max(decode(mc1,'c1',val)) c1,max(decode(mc1,'c2',val)) c2,max(decode(mc1,'c3',val)) c3
from test
group by dm1,dm2
此外,横表转纵表亦可用与decode意义相似的case语句实现,原理同该语句,这里不再过多描述。
例子2:
pid:产品ID
sid:仓库ID
pnum:库存量
create table glj_test(
pid number(3) not null,
sid varchar2(32) not null,
pnum number(9) not null
)
insert into glj_test values(1,'sid_1',2);
insert into glj_test values(1,'sid_2',3);
insert into glj_test values(2,'sid_1',5);
insert into glj_test values(2,'sid_2',1);
insert into glj_test values(3,'sid_3',1);
insert into glj_test values(3,'sid_2',3);
pid sid p-num
1 sid_1 2
1 sid_2 3
2 sid_1 5
2 sid_2 1
3 sid_3 1
3 sid_3 3
写sql语句查询出如下格式的数据:
pid sid_1 sid_2 sid_3
1 2 3 0
2 5 1 0
3 0 3 1
sql:
select pid,NVL(sum(decode(sid,'sid_1',NVL(pnum,0))),0) sid_1,NVL(sum(decode(sid,'sid_2',NVL(pnum,0))),0) sid_2,
NVL(sum(decode(sid,'sid_3',NVL(pnum,0))),0) sid_3 from glj_test group by pid