sql语句，按一个字段中的值当作结果列，列出表数据，如图

select year ,count(m1) m1,count(m2) m2,count(m3) m3,count(m4) m4 from
(select year , count(1) m1,0 m2,0 m3, 0 m4 from tab where month='1' group by year
union all
select year , 0 m1,count(1) m2,0 m3, 0 m4 from tab where month='2' group by year
union all
select year , 0 m1,0 m2,count(1) m3, 0 m4 from tab where month='3' group by year
union all
select year , 0 m1,0 m2,0 m3, count(1) m4 from tab where month='4' group by year )
group by year
没有实测，有问题可以再追问追问

别名报错了，

本回答被网友采纳

TRANSFORM Sum(表1.amount) AS amount之合计
SELECT 表1.year
FROM 表1
GROUP BY 表1.year
PIVOT "M" & [month];