我们会经常有这样的需求，就是能不能把行转换成列。下面介绍下如何使用decode函数来实现。

1、例如有这样一张表：

model item time
A1 B1 30
A1 B2 40
A2 B1 50
A2 B2 60
想实现这样的效果，如何实现呢？
B1 B2
A1 30 40
A2 50 60

select model,
sum(decode(item,'B1', time)) B1,
sum(decode(item,'B2', time)) B2
from table_name
group by model;

model B1 B2
A1 30 40
A2 50 60

2、有表A, 包含 ply_no,ins_cde 字段,
里面有数据
ply_no ins_cde
2006 1001
2006 1002
2006 1003
2005 1002
2005 1001
2004 1002
2003 1001
2000 1002

现在要从 A中找出 ins_cde仅仅等于 1001 和1002的数据,这个该如何查找?
也就是说,要找出ply_no=2005的纪录,
原文出处http://www.itpub.net/715730.html

create table my_test
(ply_no varchar2(6),
ins_cde varchar2(6));

insert into my_test values('2006','1001');
insert into my_test values('2006','1002');
insert into my_test values('2006','1003');
insert into my_test values('2005','1002');
insert into my_test values('2005','1001');
insert into my_test values('2004','1002');
insert into my_test values('2003','1001');
insert into my_test values('2000','1002');

select ply_no from my_test group by ply_no
Having sum(decode(ins_cde, 1001, 1, 1002,1)) = 2

巧妙的使用decode函数还可以提高查询性能，如有很多人会这样进行统计。下面是我同事写的sql

--丢失的份数
select count(eba128)
into n_lose
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '02';
--破损的份数
select count(eba128)
into n_damage
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '03';
--作废的份数
select count(eba128)
into n_disuse
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '04';
--长期未缴款的份数
select count(eba128)
into n_notm
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '05';
--其他的份数
select count(eba128)
into n_other
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '06';
--空白的份数
select count(eba128)
into n_blank
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 and
eba128 = '08';
--非正常核销的份数
select count(eba128)
into n_abnormal
from eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 ;
我给修改成了下面的语句，这样看着也是比较舒服的，要不代码会很臃肿！
Select Count(1) total,
Count(decode(eba128,'05',1)) total05,
Count(decode(eba128,'06',1)) total06,
Count(decode(eba128,'02',1)) total02,
Count(decode(eba128,'03',1)) total03,
Count(decode(eba128,'04',1)) total04,
Count(decode(eba128,'08',1)) total08
From eba36
where eba124 = str_eba124 and
eba002 = str_eba002 and
eba083 >= str_eba083 and
eba083 <= str_eba084 ;

希望能对大家有所帮助！