1.准备数据
create table test2(
year int,
month int,
amount int
)
insert into test2(year,month,amount) values(1991,1,1.1);
insert into test2(year,month,amount) values(1991,2,1.2);
insert into test2(year,month,amount) values(1991,3,1.3);
insert into test2(year,month,amount) values(1991,4,1.4);
insert into test2(year,month,amount) values(1992,1,1.1);
insert into test2(year,month,amount) values(1992,2,1.2);
insert into test2(year,month,amount) values(1992,3,1.3);
insert into test2(year,month,amount) values(1992,4,1.4);
select * from test2;
行转列SQL语句
MAX和sum函数都可以
--方法1 行转列
SELECT year as '年',
sum(CASE month WHEN 1 THEN amount ELSE 0 END ) '一月',
sum(CASE month WHEN 2 THEN amount ELSE 0 END ) '二月',
sum(CASE month WHEN 3 THEN amount ELSE 0 END ) '三月',
sum(CASE month WHEN 4 THEN amount ELSE 0 END ) '四月'
FROM test2
GROUP B