本文共 2357 字，大约阅读时间需要 7 分钟。

根据上图A表和B表，按照年份和地区生成1至12个月的数据，结果如下：

方法一：

select YEAR,AreaName,MAX(case Month when '1' then Money else 0 end) as [1月],MAX(case Month when '2' then Money else 0 end) as [2月],MAX(case Month when '3' then Money else 0 end) as [3月],MAX(case Month when '4' then Money else 0 end) as [4月],MAX(case Month when '5' then Money else 0 end) as [5月],MAX(case Month when '6' then Money else 0 end) as [6月],MAX(case Month when '7' then Money else 0 end) as [7月],MAX(case Month when '8' then Money else 0 end) as [8月],MAX(case Month when '9' then Money else 0 end) as [9月],MAX(case Month when '10' then Money else 0 end) as [10月],MAX(case Month when '11' then Money else 0 end) as [11月],MAX(case Month when '12' then Money else 0 end) as [12月]from (select B.AreaName, SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Monthfrom A left join B on A.AreaId=B.AreaIdgroup by AreaName, YEAR(CreateOn),Month(CreateOn)) A group by Year,AreaNameorder by Year,AreaName

select Year,AreaName,ISNULL([1],0) as [1月],ISNULL([2],0) as [2月],ISNULL([3],0) as [3月],ISNULL([4],0) as [4月],ISNULL([5],0) as [5月],ISNULL([6],0) as [6月],ISNULL([7],0) as [7月],ISNULL([8],0) as [8月],ISNULL([9],0) as [9月],ISNULL([10],0) as [10月],ISNULL([11],0) as [11月],ISNULL([12],0) as [12月]from (select B.AreaName, SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Monthfrom A left join B on A.AreaId=B.AreaIdgroup by AreaName, YEAR(CreateOn),Month(CreateOn))A pivot(	sum(money)	for Month in	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as pvtorder by Year,AreaName

--先创建表变量，并插入月份数据declare @tb table(Month varchar(2)) insert @tb select 1 union all select 2 union all select 3 union all select 4 union allselect 5 union all select 6 union all select 7 union all select 8 union allselect 9 union all select 10 union all select 11 union all select 12declare @sql varchar(8000)set @sql = 'select Year,AreaName 'select @sql = @sql + ' , max(case Month when ''' + convert(varchar(10),Month) + ''' then Money else 0 end) [' + convert(varchar(10),Month) + '月]'from (     select Month from @tb    --select distinct Month(CreateOn) Month from A  ) as aset @sql = @sql + '  from (  select B.AreaName,   SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Month  from A left join B on A.AreaId=B.AreaId  group by AreaName, YEAR(CreateOn),Month(CreateOn))tb group by Year,AreaName'exec(@sql)

转载地址：http://blzrf.baihongyu.com/