创建交叉报表(oracle)

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

创建交叉报表

create table t1(

goodid number(10) not null,

saledate date not null,

salesum number(10)

);

要求生成本年度每个月的产品销售状况表

m1 m2 m3 ... m12

g1

g2

.

.

.

gn

下面是生成报表的sql

SELECT goodid,

SUM(decode(to_char(saledate,'mm'),'01',salesum)) "01",

SUM(decode(to_char(saledate,'mm'),'02',salesum)) "02",

SUM(decode(to_char(saledate,'mm'),'03',salesum)) "03",

SUM(decode(to_char(saledate,'mm'),'04',salesum)) "04",

SUM(decode(to_char(saledate,'mm'),'05',salesum)) "05",

SUM(decode(to_char(saledate,'mm'),'06',salesum)) "06",

SUM(decode(to_char(saledate,'mm'),'07',salesum)) "07",

SUM(decode(to_char(saledate,'mm'),'08',salesum)) "08",

SUM(decode(to_char(saledate,'mm'),'09',salesum)) "09",

SUM(decode(to_char(saledate,'mm'),'10',salesum)) "10",

SUM(decode(to_char(saledate,'mm'),'11',salesum)) "11",

SUM(decode(to_char(saledate,'mm'),'12',salesum)) "12"

from t1

where to_char(saledate,'yyyy') = '2004'

group by goodid

order by goodid;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航