？？？ 请教，如何自动生成此表 ？？？
传入参数diqu，niandu，
表T1
diqu jine1 jine2 riqi flag
北京 100 50 2010-01-10 1
北京 100 30 2010-05-10 1
上海 80 10 2010-01-10 1
上海 200 80 2010-05-10 0
上海 40 20 2010-07-10 1
北京 70 30 2011-01-10 1
diqu niandu jine1 jine2 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
全部 2009 0 0 0 0 0 0 0 0 0 0 0 0 0 0
全部 2010 320 110 180 0 0 0 100 0 40 0 0 0 0 0
全部 2011 70 30 70 0 0 0 0 0 0 0 0 0 0 0
diqu niandu jine1 jine2 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
北京 2009 0 0 0 0 0 0 0 0 0 0 0 0 0 0
北京 2010 200 80 100 0 0 0 100 0 0 0 0 0 0 0
北京 2011 70 30 70 0 0 0 0 0 0 0 0 0 0 0
create table #A(diqu varchar(20),jine1 int,jine2 int ,riqi date,flag int)
insert into #A
select '北京',100,50,'2010-01-10',1 union all select
'北京', 100 , 30 ,'2010-05-10', 1 union all select
'上海', 80 , 10 ,'2010-01-10', 1 union all select
'上海', 200 , 80 ,'2010-05-10', 0 union all select
'上海', 40 , 20 ,'2010-07-10', 1 union all select
'北京', 70 , 30 ,'2011-01-10', 1
--字符串分割
create function [dbo].[f_Split]
(
@SourceSql nvarchar(max),--源分隔字符串
@StrSeprate varchar(10)=','--分隔符
)
returns @temp table(a nvarchar(max))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
GO
--------------------------------测试
declare @diqu varchar(20)
declare @niandu varchar(50)
set @diqu='全部'
set @niandu='2010,2011,2012';
declare @TB table(diqu varchar(20),jine1 int,jine2 int ,riqi date,flag int)
if @diqu='全部'
insert into @TB
select '全部' diqu,jine1,jine2,riqi,flag from #A where YEAR(riqi) in (select * from f_Split(@niandu,','))
else
insert into @TB
select diqu,jine1,jine2,riqi,flag from #A where YEAR(riqi) in (select * from f_Split(@niandu,',')) and diqu in (select * from f_Split(@diqu,','))
select x.diqu,x.nian,x.jine1,x.jine2,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 diqu,YEAR(riqi) nian,SUM(case when flag=1 then jine1 else 0 end) jine1,SUM(case when flag=1 then jine2 else 0 end) jine2 from @TB group by diqu,YEAR(riqi)) as X
inner join (
select *
from (
select diqu,jine1,YEAR(riqi) nian,cast(MONTH(riqi) as varchar(2))+'月' yue
from @TB) as a
pivot(sum(jine1) for yue in ([1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月])) as B) as Y on x.diqu=y.diqu and x.nian=y.nian
