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

Question

求转换日期字段为列的交叉表并求和有一表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 特约二
转换为另一表tb1jcmc                20120401  20120402  20120403  横向求和    
51路                     ?       ?        ?          ?
机2(7)                                               
711路                                                
宝山社区罗罗                                         
淞安专线                                                
1211路      
302路       
彭罗专线    
51路        
机2(7)      
711路       
特约二竖向求和                 ?       ?        ?          ?求各位大大不吝指教      

Answer 1

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

Answer 2

在查询执行出现如下错误服务器: 消息 170，级别 15，状态 1，行 3
第 3 行: 'max' 附近有语法错误。
服务器: 消息 137，级别 15，状态 1，行 5
必须声明变量 '@str'。
服务器: 消息 137，级别 15，状态 1，行 5
必须声明变量 '@str'。
服务器: 消息 137，级别 15，状态 1，行 9
必须声明变量 '@str'。

Answer 3

把max改成8000，你的是2000的数据库，不能用max

Answer 4

declare @str varchar(8000)
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')

Answer 5

max改为8000就能用了、
还用一个问题，我用你13日给的代码执行（不是xrq转为列，而是jcmc转为列）
出现如下错误：
--> 测试数据：[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(8000)
set @str=''
declare @sql varchar(8000)
set @sql=''
select @str=@str+','+[JCMC]+'=max(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'
from tbl group by [JCMC]
select @sql=@sql+','+[JCMC]+'=sum(case when [JCMC]='+
QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'
from tbl group by [JCMC]
exec('select convert(varchar(10),[XRQ],120) as [XRQ]'+@str+
',sum([RGL]) as 横向和 from tbl group by [XRQ] union all 
select ''竖向求和'''+@sql+',sum([RGL]) from tbl')
（所影响的行数为 12 行）服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '=' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '=' 附近有语法错误。

Answer 6

改过后能用了
还有一个问题，用你昨日给的交叉表代码执行--> 测试数据：[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,'特约二'

Answer 7

还是不行，请大大看看
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([XRQ] datetime,[RGL] int,[LXMC] varchar(20))
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,'特约二'
select * from tbldeclare @str varchar(8000)
set @str=''
declare @sql varchar(8000)
set @sql=''
select @str=@str+','+[LXMC]+'=max(case when [LXMC]='+
QUOTENAME([LXMC],'''')+' then [RGL] else 0 end)'
from tbl group by [LXMC]
select @sql=@sql+','+[LXMC]+'=sum(case when [LXMC]='+
QUOTENAME([LXMC],'''')+' then [RGL] else 0 end)'
from tbl group by [LXMC]
exec('select convert(varchar(10),[XRQ],120) as [XRQ]'+@str+
',sum([RGL]) as 横向和 from tbl group by [XRQ] union all 
select ''竖向求和'''+@sql+',sum([RGL]) from tbl')（所影响的行数为 12 行）服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '=' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: '=' 附近有语法错误。

Answer 8

是不是数据库是sql server2000个人版的问题（系统是XP）我把前日的数据测试就能通过，换个数据内容就不行