根据上图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) Month
from A left join B on A.AreaId=B.AreaId
group by AreaName, YEAR(CreateOn),Month(CreateOn)
) A group by Year,AreaName
order 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) Month
from A left join B on A.AreaId=B.AreaId
group 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 pvt
order 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 all
select 5 union all select 6 union all select 7 union all select 8 union all
select 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 a
set @sql = @sql + ' 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)
)
tb group by Year,AreaName'
--print @sql
exec(@sql)
