？？？求高手帮忙，如何完成下面SQL语句？？？
传入参数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参数为2009, 2010, 2011 则统计结果如下:
其中jine1为flag=1的该年度jine1的总和，jine2也相同，1月~12月指此年度该月jine1的合计数，月以riqi判断
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参数为2009, 2010, 2011 则统计结果如下:
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
请问如何根据传入参数，类似（2009,2010,2011; 全部）自动生成上表？
[最优解释]
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 ,nian int,yue varchar(20),flag int)
if @diqu='全部'
insert into @TB
select '全部' diqu,jine1,jine2,YEAR(riqi) nian,MONTH(riqi) yue,flag from #A where YEAR(riqi) in (select * from f_Split(@niandu,','))
else
insert into @TB
select diqu,jine1,jine2,YEAR(riqi) nian,MONTH(riqi) yue,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,[1月],[2月],[3月],[4月],[5月], [6月],[7月], [8月],[9月],[10月],[11月], [12月]
from (select diqu, 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,nian) as X
inner join (
select diqu,nian,SUM(case when yue=1 then jine1 else 0 end) as [1月]
,SUM(case when yue=2 then jine1 else 0 end) as [2月],
SUM(case when yue=3 then jine1 else 0 end) as [3月],
SUM(case when yue=4 then jine1 else 0 end) as [4月],
SUM(case when yue=5 then jine1 else 0 end) as [5月],
SUM(case when yue=6 then jine1 else 0 end) as [6月],
SUM(case when yue=7 then jine1 else 0 end) as [7月],
SUM(case when yue=8 then jine1 else 0 end) as [8月],
SUM(case when yue=9 then jine1 else 0 end) as [9月],
SUM(case when yue=10 then jine1 else 0 end) as [10月],
SUM(case when yue=11 then jine1 else 0 end) as [11月],
SUM(case when yue=12 then jine1 else 0 end) as [12月]
from @TB
group by diqu,nian) as Y on x.diqu=y.diqu and x.nian=y.nian
[其他解释]
case when 吧
[其他解释]
动态生成列，动态行列转换，搞定……
[其他解释]
create function [dbo].[f_Split]
(
@SourceSql nvarchar(2000),--源分隔字符串
@StrSeprate varchar(10)--分隔符
)
returns @temp table(a nvarchar(2000))
as
把max 换掉
[其他解释]
Select * from dbo.f_Split('2009,2010,2011',',')
--这是正确的。将字符串以逗号分隔
