日期:2014-05-18 浏览次数:20851 次
--> --> (Roy)生成測試數據 set nocount on; if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([XRQ] Datetime,[RYU] decimal(18,1),[RGL] int,[JCMC] nvarchar(5)) Insert #T select '2012-04-03',8072.0,25522,N'_1分公司' union all select '2012-04-02',8380.2,26913,N'_2分公司' union all select '2012-04-01',8451.6,26572,N'_1分公司' union all select '2012-04-03',8262.2,26527,N'_2分公司' union all select '2012-04-02',8265.7,26048,N'_1分公司' union all select '2012-04-01',8378.7,26896,N'_2分公司' Go declare @str varchar(8000) set @str='' select @str=@str+','+quotename([JCMC]+'(RYU)')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RYU] else 0 end)'+',' +quotename([JCMC]+'(RGL)')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'+',' +quotename([JCMC]+'(RYU/RGL)')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then cast([RYU]*100.0/[RGL] as decimal(18,1)) else 0 end)' from #T group by [JCMC] print @str exec('select isnull(convert(varchar(10),[XRQ],120),''合计'') as [XRQ]'+@str+ ',sum([RYU]) as 横RYU计,sum([RGL]) as 横RGL计,str(sum([RYU])*100.0/sum([RGL]),5,2)+''%'' as 百分比 from #T group by [XRQ] with rollup') /* XRQ _1分公司(RYU) _1分公司(RGL) _1分公司(RYU/RGL) _2分公司(RYU) _2分公司(RGL) _2分公司(RYU/RGL) 横RYU计 横RGL计 百分比 2012-04-01 8451.6 26572 31.8 8378.7 26896 31.2 16830.3 53468 31.48% 2012-04-02 8265.7 26048 31.7 8380.2 26913 31.1 16645.9 52961 31.43% 2012-04-03 8072.0 25522 31.6 8262.2 26527 31.1 16334.2 52049 31.38% 合计 8451.6 26572 31.8 8380.2 26913 31.2 49810.4 158478 31.43% */