select * from store.all_sales;
数据类型如下：
序号年份月份类型收入
1 2003 1 1 10034.84
2 2003 2 1 15144.65
3 2003 3 1 20137.83
4 2003 4 1 25057.45
5 2003 5 1 17214.56
6 2003 6 1 15564.64
7 2003 7 1 12654.84
8 2003 8 1 17434.82
9 2003 9 1 19854.57
10 2003 10 1 21754.19
11 2003 11 1 13029.73
12 2003 12 1 10034.84
select *
from (select year as 年份,
month as 月份,
prd_type_id as 类型,
nvl(amount, 0) as amount
from store.all_sales)
pivot(sum(nvl(amount, 0))
for(月份) in(1 as 一月,
2 as 二月,
3 as 三月,
4 as 四月,
5 as 五月,
6 as 六月,
7 as 七月,
8 as 八月,
9 as 九月,
10 as 十月,
11 as 十一月,
12 as 十二月))
order by 年份, 类型;
查询结果如下：
序号年份月份一月二月三月四月五月六月
1 2003 1 38909.04 70567.9 91826.98 120344.7 97287.36 57387.84 60929.04 75608.92 85027.42 105305.22 55678.38 46209.04
2 2003 2 14309.04 13367.9 16826.98 15664.7 18287.36 14587.84 15689.04 16308.92 19127.42 13525.14 16177.84 12509.04
3 2003 3 24909.04 15467.9 20626.98 23844.7 18687.36 19887.84 81589.04 62408.92 46127.42 70325.29 46187.38 48209.04
4 2003 4 17398.43 17267.9 31026.98 16144.7 20087.36 33087.84 12089.04 58408.92 49327.42 75325.14 42178.38 30409.05
5 2003 5 0 0 0 0 0 0 0 0 0 0 0 0

ITPub博客

行转列