日期:2014-05-18  浏览次数:20472 次

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

有一表fgsgl_sb
xrq rgl jcmc
2012-04-01 4182 51路  
2012-04-01 598 机2(7)  
2012-04-01 2520 711路  
2012-04-02 258 宝山社区罗罗
2012-04-02 5092 淞安专线  
2012-04-02 504 1211路  
2012-04-02 778 302路  
2012-04-03 2515 彭罗专线  
2012-04-03 3852 51路  
2012-04-03 148 机2(7)  
2012-04-03 1930 711路  
2012-04-03 50 特约二


转换为另一表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 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
*/