创建交叉报表(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;