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

--> 测试数据：[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 allselect '2012-04-01',598,'机2(7)' union allselect '2012-04-01',2520,'711路' union allselect '2012-04-02',258,'宝山社区罗罗' union allselect '2012-04-02',5092,'淞安专线' union allselect '2012-04-02',504,'1211路' union allselect '2012-04-02',778,'302路' union allselect '2012-04-03',2515,'彭罗专线' union allselect '2012-04-03',3852,'51路' union allselect '2012-04-03',148,'机2(7)' union allselect '2012-04-03',1930,'711路' union allselect '2012-04-03',50,'特约二'declare @str varchar(max)set @str=''select @[email protected]+',['+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 [email protected]+',sum(rgl) as [横向和] from [tbl] group by [jcmc] with rollup')/*xrq    20120401    20120402    20120403    横向和1211路    0    504    0    504302路    0    778    0    77851路    4182    0    3852    8034711路    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*/