ORACLE行转列

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分组统计当月每天各种投诉原因的数量）：