使用动态SQL语句实现简单的行列转置（动态产生列）

原始数据如下图所示：（商品的销售明细）
date=业务日期；Item=商品名称；saleqty=销售数量；

-- 建立测试数据（表）
create table test (Date varchar(10), item char(10),saleqty int)
insert test values('2010-01-01','AAA',8)
insert test values('2010-01-02','AAA',4)
insert test values('2010-01-03','AAA',5)
insert test values('2010-01-01','BBB',1)
insert test values('2010-01-02','CCC',2)
insert test values('2010-01-03','DDD',6)

需要实现的报表样式：每一行既每一天，显示所有商品（列）该天的销售数量；

实现的方法和思路如下：

-- 实现结果的静态SQL语句写法
-- 整理报表需要的格式

select date1,
case item when 'AAA' then saleqty when null then 0 end as AAA,
case item when 'BBB' then saleqty when null then 0 end as BBB,
case item when 'CCC' then saleqty when null then 0 end as CCC,
case item when 'DDD' then saleqty when null then 0 end as DDD
from test

-- 按日期汇总行

select date1,
sum(case item when 'AAA' then saleqty when null then 0 end) as AAA,
sum(case item when 'BBB' then saleqty when null then 0 end) as BBB,
sum(case item when 'CCC' then saleqty when null then 0 end) as CCC,
sum(case item when 'DDD' then saleqty when null then 0 end) as DDD
from test 
group by date1

-- 处理数据：将空值的栏位填入数字0；

select date1,
NVL (sum(case item when 'AAA' then saleqty end),0) as AAA,
NVL (sum(case item when 'BBB' then saleqty end),0) as BBB,
NVL (sum(case item when 'CCC' then saleqty end),0) as CCC,
NVL (sum(case item when 'DDD' then saleqty end),0) as DDD
from test 
group by date1