从Oracle 表格行列转置说起

为了符合阅读习惯，最终报表希望是如下格式：

------------------------

咱们一步步来实现：

1.运用DECODE转换行为列

SQL：

SELECT NO,
    DECODE(DAY,1,MONEY,'') DAY1,
    DECODE(DAY,2,MONEY,'') DAY2,
    DECODE(DAY,3,MONEY,'') DAY3
FROM TEMP

结果：

2.按NO字段分组，并更改列名

SQL：

SELECT NO, MAX(DAY1) MON, MAX(DAY2) TUE, MAX(DAY3) THR
 FROM (SELECT NO,
        DECODE(DAY, 1, MONEY,'') DAY1,
        DECODE(DAY, 2, MONEY,'') DAY2,
        DECODE(DAY, 3, MONEY,'') DAY3
     FROM TEMP)
 GROUP BY NO;

结果：

------------------------

重难点归纳：

1.DECODE缺省值设置

DECODE语法如下：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

如果缺省值由''（两个单引号）改为0，即SQL：

SELECT NO, MAX(DAY1) MON, MAX(DAY2) TUE, MAX(DAY3) THR
 FROM (SELECT NO,
        DECODE(DAY, 1, MONEY,0) DAY1,
        DECODE(DAY, 2, MONEY,0) DAY2,
        DECODE(DAY, 3, MONEY,0) DAY3
     FROM TEMP)
 GROUP BY NO;

结果如下（所有值为负与空值都被赋为0）：

2.列缺省值设置（DAY值为8的显示为'undefined'）

SQL：

SELECT NO,MONEY,
    DECODE(DAY,1,'MON',2,'TUE',3,'THR','undefined') DAY
FROM TEMP

结果：

3.行列转化在表单内数据量较大的情况下消耗较大

原因：

1.扫描目标数据时间开销大。

2.GROUP BY时，数据冗余带来的多行合并。

优点：

表结构稳定：DAY增加新值只需增加记录，无需新增新列！

下一页 decode()函數使用技巧 

decode()函數使用技巧 
·软件环境： 
1、Windows NT4.0+ORACLE 8.0.4 
2、ORACLE安装路径为：C:\ORANT 
·含义解释： 
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 

该函数的含义如下： 
IF 条件=值1 THEN 
　　　　RETURN(翻译值1) 
ELSIF 条件=值2 THEN 
　　　　RETURN(翻译值2) 
　　　　...... 
ELSIF 条件=值n THEN 
　　　　RETURN(翻译值n) 

ELSE 
　　　　RETURN(缺省值) 
END IF 
· 使用方法： 
1、比较大小 
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值 
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 

例如： 
变量1=10，变量2=20 
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。 


2、表、视图结构转化 
现有一个商品销售表sale，表结构为： 
month　　　 char(6)　　　　　 --月份 
sell　　　　number(10,2)　　　--月销售金额 

现有数据为： 
200001　　1000 
200002　　1100 
200003　　1200 
200004　　1300 
200005　　1400 
200006　　1500 
200007　　1600 
200101　　1100 
200202　　1200 
200301　　1300 

想要转化为以下结构的数据： 
year　　　char(4)　　　　　 --年份 
month1　　number(10,2)　　　--1月销售金额 
month2　　number(10,2)　　　--2月销售金额 
month3　　number(10,2)　　　--3月销售金额 
month4　　number(10,2)　　　--4月销售金额 
month5　　number(10,2)　　　--5月销售金额 
month6　　number(10,2)　　　--6月销售金额 
month7　　number(10,2)　　　--7月销售金额 
month8　　number(10,2)　　　--8月销售金额 
month9　　number(10,2)　　　--9月销售金额 
month10　　number(10,2)　　　--10月销售金额 
month11　　number(10,2)　　　--11月销售金额 
month12　　number(10,2)　　　--12月销售金额 

结构转化的SQL语句为： 
create or replace view 
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12) 
as 
　　　　select 
　　　　substrb(month,1,4), 
　　　　sum(decode(substrb(month,5,2),'01',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'02',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'03',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'04',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'05',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'06',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'07',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'08',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'09',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'10',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'11',sell,0)), 
　　　　sum(decode(substrb(month,5,2),'12',sell,0)) 
　　　　from sale 
　　　　group by substrb(month,1,4);