将纵表转换为横表
创建表
create table sale(
month char(6), -- 月份
sell numeric(10,2) -- 月销售金额
);
desc sale;
添加数据
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
select * from sale;
转换格式
select substring(month,1,4) '年份',
sum(case substring(month,5,2) when '01' then sell end) '一月',
sum(case substring(month,5,2) when '02' then sell end) '二月',
sum(case substring(month,5,2) when '03' then sell end) '三月',
sum(case substring(month,5,2) when '04' then sell end) '四月',
sum(case substring(month,5,2) when '05' then sell end) '五月',
sum(case substring(month,5,2) when '06' then sell end) '六月',
sum(case substring(month,5,2) when '07' then sell end) '七月',
sum(case substring(month,5,2) when '08' then sell end) '八月',
sum(case substring(month,5,2) when '09' then sell end) '九月',
sum(case substring(month,5,2) when '10' then sell end) '十月',
sum(case substring(month,5,2) when '11' then sell end) '十一月',
sum(case substring(month,5,2) when '12' then sell end) '十二月'
from sale group by substring(month,1,4);