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

上一个：在oracle 数据库中查看一个sql语句的执行时间和SP2-0027错误
下一个：Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法