SqlServer 行转列（统计某年一到十二个月数据总和）

最新推荐文章于 2023-03-13 17:21:10 发布

b150030

最新推荐文章于 2023-03-13 17:21:10 发布

阅读量1.6k

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/ZHANGKAIXUAN/p/6087035.html

版权

该博客介绍了一种使用SQL Server将数据按月份转换为列的方法，以统计一年中每个月的销售额总和。通过CASE语句结合SUM函数，分别计算1月至12月的SumMoney，然后使用UNPIVOT操作将结果转换为单一列展示。

摘要由CSDN通过智能技术生成

select * from(
select
sum(case MONTH(purchase_date) when '1' then SumMoney else 0 end) as January,
sum(case MONTH(purchase_date) when '2' then SumMoney else 0 end) as February,
sum(case MONTH(purchase_date) when '3' then SumMoney else 0 end) as March,
sum(case MONTH(purchase_date) when '4' then SumMoney else 0 end) as April,
sum(case MONTH(purchase_date) when '5' then SumMoney else 0 end) as May,
sum(case MONTH(purchase_date) when '6' then SumMoney else 0 end) as June,
sum(case MONTH(purchase_date) when '7' then SumMoney else 0 end) as July,
sum(case MONTH(purchase_date) when '8' then SumMoney else 0 end) as August,
sum(case MONTH(purchase_date) when '9' then SumMoney else 0 end) as September,
sum(case MONTH(purchase_date) when '10' then SumMoney else 0 end) as October,
sum(case MONTH(pu