交叉表求和，百分比
XRQ RYU RGL JCMC
2012-04-03 8072.0 25522 _1分公司
2012-04-02 8380.2 26913 _2分公司
2012-04-01 8451.6 26572 _1分公司
2012-04-03 8262.2 26527 _2分公司
2012-04-02 8265.7 26048 _1分公司
2012-04-01 8378.7 26896 _2分公司
执行如下代码
declare @str varchar(8000)
set @str=''
declare @sql varchar(8000)
set @sql=''
select @[email protected]+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RYU] else 0 end)'+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then cast([RYU]/[RGL]*100 as decimal(18,1)) else 0 end)'
from tbl group by [JCMC]
select @[email protected]+','+[JCMC]+'=sum(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RYU] else 0 end)'+','+[JCMC]+'=sum(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then cast([RYU]/[RGL]*100 as decimal(18,1)) else 0 end)'
from tbl group by [JCMC]
exec('select convert(varchar(10),[XRQ],120) as [XRQ][email protected]+
',sum([RYU]) as 横RYU计 from tbl group by [XRQ] union all
select [email protected]+',sum([RYU]) from tbl')
得到交叉表：
XRQ _1分公司 _1分公司 _1分公司 _2分公司 _2分公司 _2分公司 横RYU计 横rgl %
2012-04-01 8451.6 26572 31.8 8378.7 26896 31.2 16830.3 ? ?
2012-04-02 8265.7 26048 31.7 8380.2 26913 31.1 16645.9 ? ?
2012-04-03 8072.0 25522 31.6 8262.2 26527 31.1 16334.2 ? ?
竖向合计 24789.3 78142 31.8 25021.1 80336 31.2 49810.4 ? ?
请大大们帮助解决：
1,得到横rgl
2,得到横RYU除以横rgl乘以100的百分比
3,列表头能区分，如:_1分公司ryu,_1分公司rgl,_1分公司%,以此类推
------解决方案--------------------
- SQL code
--> --> (Roy)生成測試數據set nocount on;if not object_id('Tempdb..#T') is null drop table #TGoCreate table #T([XRQ] Datetime,[RYU] decimal(18,1),[RGL] int,[JCMC] nvarchar(5))Insert #Tselect '2012-04-03',8072.0,25522,N'_1分公司' union allselect '2012-04-02',8380.2,26913,N'_2分公司' union allselect '2012-04-01',8451.6,26572,N'_1分公司' union allselect '2012-04-03',8262.2,26527,N'_2分公司' union allselect '2012-04-02',8265.7,26048,N'_1分公司' union allselect '2012-04-01',8378.7,26896,N'_2分公司'Godeclare @str varchar(8000)set @str=''select @[email protected]+','+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 @strexec('select isnull(convert(varchar(10),[XRQ],120),''合计'') as [XRQ][email protected]+',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%*/