oracle 创交表,创建交叉报表(oracle)_oracle

创建交叉报表

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;

欢迎大家阅读《创建交叉报表(oracle)_oracle》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~