求转换日期字段为列的交叉表并求和,该怎么处理

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([xrq] datetime,[rgl] int,[jcmc] varchar(12))
insert [tbl]
select '2012-04-01',4182,'51路' union all
select '2012-04-01',598,'机2(7)' union all
select '2012-04-01',2520,'711路' union all
select '2012-04-02',258,'宝山社区罗罗' union all
select '2012-04-02',5092,'淞安专线' union all
select '2012-04-02',504,'1211路' union all
select '2012-04-02',778,'302路' union all
select '2012-04-03',2515,'彭罗专线' union all
select '2012-04-03',3852,'51路' union all
select '2012-04-03',148,'机2(7)' union all
select '2012-04-03',1930,'711路' union all
select '2012-04-03',50,'特约二'



declare @str varchar(max)
set @str=''
select @str=@str+',['+replace(convert(varchar(10),[xrq],120),'-','')+']'+
'=sum(case when [xrq]='+quotename(replace(convert(varchar(10),[xrq],120),'-','')
,'''')+' then [rgl] else 0 end)'
from tbl group by replace(convert(varchar(10),[xrq],120),'-',''),[xrq]
exec('select isnull([jcmc],''竖向求和'') as xrq'+@str
+',sum(rgl) as [横向和] from [tbl] group by [jcmc] with rollup')

/*
xrq    20120401    20120402    20120403    横向和
1211路    0    504    0    504
302路    0    778    0    778
51路    4182    0    3852    8034
711路    2520    0    1930    4450
宝山社区罗罗    0    258    0    258
机2(7)    598    0    148    746
彭罗专线    0    0    2515    2515
淞安专线    0    5092    0    5092
特约二    0    0    50    50
竖向求和    7300    6632    8495    22427
*/