ORACLE行转列

Oracle行转列用到decode函数，decode函数说明如下：

decode(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n，缺省值)

decode(字段，比较1，翻译值1，比较2，翻译值2，...比较n，翻译值n，缺省值)

该函数含义如下：

if ? ?条件==值1 ? ?then

return（翻译值1）

elsif ? ?条件==值2 ? ?then

return（翻译值2）

...

else

return(缺省值)

end if

例子：select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;--取较小的值

再此说明一下sign(n)函数：

该函数取数字n的符号，如果n大于0返回1，等于0返回0，小于0返回-1.

以下为工作中遇到的需要进行行转列显示数据的经历：

用户投诉表结构如下：

-- Create table
create table CMP_TOUSU
(
?id ? ? INTEGER not null,
?cpid ? INTEGER,
?cpname VARCHAR2(30),
?dtime ?VARCHAR2(50),
?r1 ? ? INTEGER default 0,
?r2 ? ? INTEGER default 0,
?r3 ? ? INTEGER default 0,
?r4 ? ? INTEGER default 0
)
tablespace USERS
?pctfree 10
?initrans 1
?maxtrans 255
?storage
?(
? ?initial 64
? ?next 1
? ?minextents 1
? ?maxextents unlimited
?);
-- Add comments to the columns?
comment on column CMP_TOUSU.dtime
?is '投诉时间';
comment on column CMP_TOUSU.r1
?is '全部投诉';
comment on column CMP_TOUSU.r2
?is '否认定制投诉';
comment on column CMP_TOUSU.r3
?is '定制争议投诉';
comment on column CMP_TOUSU.r4
?is '商品未到账投诉';
-- Create/Recreate primary, unique and foreign key constraints?
alter table CMP_TOUSU
?add constraint PK_CMP_TOUSU primary key (ID)
?using index?
?tablespace USERS
?pctfree 10
?initrans 2
?maxtrans 255
?storage
?(
? ?initial 64K
? ?next 1M
? ?minextents 1
? ?maxextents unlimited
?);

执行如下sql语句：select * from cmp_tousu where dtime between '2014-10-01' and '2014-10-31';

结果如下所示：

执行sql语句：select cpname,sum(decode(dtime,'2014-10-01',r1,0)) day1,sum(decode(dtime,'2014-10-02',r1,0)) day2,sum(decode(dtime,'2014-10-03',r1,0)) day3,
sum(decode(dtime,'2014-10-04',r1,0)) day4,sum(decode(dtime,'2014-10-05',r1,0)) day5,sum(decode(dtime,'2014-10-06',r1,0)) day6,sum(decode(dtime,'2014-10-07',r1,0)) day7,
sum(decode(dtime,'2014-10-08',r1,0)) day8,sum(decode(dtime,'2014-10-09',r1,0)) day9,sum(decode(dtime,'2014-10-10',r1,0)) day10,sum(decode(dtime,'2014-10-011',r1,0)) day11,
sum(decode(dtime,'2014-10-12',r1,0)) day12,sum(decode(dtime,'2014-10-13',r1,0)) day13,sum(decode(dtime,'2014-10-14',r1,0)) day14,sum(decode(dtime,'2014-10-15',r1,0)) day15,
sum(decode(dtime,'2014-10-16',r1,0)) day16,sum(decode(dtime,'2014-10-17',r1,0)) day17,sum(decode(dtime,'2014-10-18',r1,0)) day18,sum(decode(dtime,'2014-10-19',r1,0)) day19,
sum(decode(dtime,'2014-10-20',r1,0)) day20,sum(decode(dtime,'2014-10-21',r1,0)) day21,sum(decode(dtime,'2014-10-22',r1,0)) day22,sum(decode(dtime,'2014-10-23',r1,0)) day23,
sum(decode(dtime,'2014-10-24',r1,0)) day24,sum(decode(dtime,'2014-10-25',r1,0)) day25,sum(decode(dtime,'2014-10-26',r1,0)) day26,sum(decode(dtime,'2014-10-27',r1,0)) day27,
sum(decode(dtime,'2014-10-28',r1,0)) day28,sum(decode(dtime,'2014-10-29',r1,0)) day29,sum(decode(dtime,'2014-10-30',r1,0)) day30,sum(decode(dtime,'2014-10-31',r1,0)) day31
from cmp_tousu where dtime between '2014-10-01' and '2014-10-31' group by cpname?

行转列效果如下图所示（按cpname分组统计当月每天各种投诉原因的数量）：

cs