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

 

原文转自：http://www.ltesting.net