求转换日期字段为列的交叉表并求和
求转换日期字段为列的交叉表并求和

有一表fgsgl_sb
xrq rgl jcmc
2012-04-01418251路
2012-04-01598机2(7)
2012-04-012520711路
2012-04-02258宝山社区罗罗
2012-04-025092淞安专线
2012-04-025041211路
2012-04-02778302路
2012-04-032515彭罗专线
2012-04-03385251路
2012-04-03148机2(7)
2012-04-031930711路
2012-04-0350特约二


转换为另一表tb1

jcmc 20120401 20120402 20120403 横向求和
51路 ? ? ? ?
机2(7)
711路
宝山社区罗罗
淞安专线
1211路
302路
彭罗专线
51路
机2(7)
711路
特约二

竖向求和 ? ? ? ?

求各位大大不吝指教




[解决办法]

SQL code

--> 测试数据：[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 @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    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*/