从Oracle 表格行列转置说起第1/2页

NO	MONEY	DAY
1	23	1
1	43	2
1	-45	3
2	42	1
2	-10	2
2	50	3
3	100	8

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

NO	MON	TUE	THR
1	23	43	-45
2	42	-10	50
3

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

咱们一步步来实现：

1.运用DECODE转换行为列

SQL：

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

结果：

NO	DAY1	DAY2	DAY3
1	23
1		43
1			-45
2	42
2		-10
2			50
3

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;

结果：

NO	MON	TUE	THR
1	23	43	-45
2	42	-10	50
3

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

重难点归纳：

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）：

NO	MON	TUE	THR
1	23	43	0
2	42	0	50
3	0	0	0

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

SQL：

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

结果：

NO	MONEY	DAY
1	23	MON
1	43	TUE
1	-45	THR
2	42	MON
2	-10	TUE
2	50	THR
3	100	undefined

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);

声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。