求教大神行转列合计问题

weixin_39402825 2017-09-13 03:25:01 
名称  号码   CD     日期      金额  	

a	 1	 A	1991-01-01	500

a	 1	 C    1992-01-01	0

a	 3	 C	1993-01-01	0

a	 1	 A	1994-01-01	400

a	 1	 A	1995-01-01	300

a	 2	 B	1996-01-01	0

b	 1	 A	1997-01-01	0

b	 1	 B	1998-01-01	0

b	 1	 C	1999-01-01	0

b	 2	 A	2001-01-01	0

b	 2	 B	2002-01-01	500

b	 2	 B	2003-01-01	500

b	 2	 C	2004-01-01	0

b	 3	 A	2005-01-01	0

b	 3	 B	2006-01-01	0 

..    ..    ..   ...........  ..

以上数据根据名称，号码，CD来分组，日期(数万条)的行转为列并合计金额 大概效果如下 请问该怎么实现啊
名称 号码 CD 1991-01-01  1992-01-01  1993-01-01 1994-01-01  1995-01-01 .........

a	1	A	  500

a	1	C	   

a	2	A	                                    400         300

a	2	B	 

a	3	C	 

b	1	A	 

b	1	B	 

b	1	C	 

b	2	A	 

b	2	B	  

b	2	C	  500

b	3	A	 

b	3	B
[/align][/align]
...全文
585 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
山城忙碌人 2017-09-14
  • 打赏
  • 举报
 回复

null 用isnull转一下。
山城忙碌人 2017-09-14
  • 打赏
  • 举报
 回复
select * from ( select name,phone,dt,je,cd from t ) as source PIVOT ( sum(je) for[dt] in ([1991-01-01],[1992-01-01],[1993-01-01],[1994-01-01],[1995-01-01],[1996-01-01]) ) as pivotData 这是不是你想要的？
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 21 楼 sinat_28984567 的回复:
[quote=引用 20 楼 weixin_39402825 的回复:] [quote=引用 19 楼 sinat_28984567 的回复:] 好吧……没碰到过空格变问号这个情况…… 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
DECLARE @sql NVARCHAR(MAX)='select?名称,号码,CD?,
?sum(case?日期?when?''1991-01-01''?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?''1992-01-01''?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?''1993-01-01''?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?''1994-01-01''?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?''1995-01-01''?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?''1996-01-01''?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?''1997-01-01''?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?''1998-01-01''?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?''1999-01-01''?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?''2001-01-01''?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?''2002-01-01''?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?''2003-01-01''?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?''2004-01-01''?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?''2005-01-01''?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?''2006-01-01''?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
EXEC(@sql)
那暂时是无解了吗 好吧谢谢了虽然没解决不过也学习了 真心感谢！辛苦了大神大哥[/quote] 这样执行也还是有问号是吗？[/quote] 嗯啊
繁花尽流年 2017-09-13
  • 打赏
  • 举报
 回复
怎么感觉自动把空格换成问号一样，字库问题？
二月十六 2017-09-13
  • 打赏
  • 举报
 回复
引用 20 楼 weixin_39402825 的回复:
[quote=引用 19 楼 sinat_28984567 的回复:] 好吧……没碰到过空格变问号这个情况…… 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
DECLARE @sql NVARCHAR(MAX)='select?名称,号码,CD?,
?sum(case?日期?when?''1991-01-01''?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?''1992-01-01''?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?''1993-01-01''?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?''1994-01-01''?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?''1995-01-01''?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?''1996-01-01''?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?''1997-01-01''?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?''1998-01-01''?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?''1999-01-01''?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?''2001-01-01''?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?''2002-01-01''?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?''2003-01-01''?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?''2004-01-01''?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?''2005-01-01''?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?''2006-01-01''?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
EXEC(@sql)
那暂时是无解了吗 好吧谢谢了虽然没解决不过也学习了 真心感谢！辛苦了大神大哥[/quote] 这样执行也还是有问号是吗？
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 19 楼 sinat_28984567 的回复:
好吧……没碰到过空格变问号这个情况…… 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
DECLARE @sql NVARCHAR(MAX)='select?名称,号码,CD?,
?sum(case?日期?when?''1991-01-01''?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?''1992-01-01''?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?''1993-01-01''?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?''1994-01-01''?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?''1995-01-01''?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?''1996-01-01''?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?''1997-01-01''?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?''1998-01-01''?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?''1999-01-01''?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?''2001-01-01''?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?''2002-01-01''?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?''2003-01-01''?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?''2004-01-01''?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?''2005-01-01''?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?''2006-01-01''?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
EXEC(@sql)
那暂时是无解了吗 好吧谢谢了虽然没解决不过也学习了 真心感谢！辛苦了大神大哥
二月十六 2017-09-13
  • 打赏
  • 举报
 回复
好吧……没碰到过空格变问号这个情况…… 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
DECLARE @sql NVARCHAR(MAX)='select?名称,号码,CD?,
?sum(case?日期?when?''1991-01-01''?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?''1992-01-01''?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?''1993-01-01''?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?''1994-01-01''?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?''1995-01-01''?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?''1996-01-01''?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?''1997-01-01''?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?''1998-01-01''?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?''1999-01-01''?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?''2001-01-01''?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?''2002-01-01''?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?''2003-01-01''?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?''2004-01-01''?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?''2005-01-01''?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?''2006-01-01''?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
EXEC(@sql)
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 17 楼 sinat_28984567 的回复:
[quote=引用 16 楼 weixin_39402825 的回复:] [quote=引用 15 楼 sinat_28984567 的回复:] 没变？在打印一下看看 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
嗯 这次打印的也跟上面的一样没变 中间掺着问号 位置也没变[/quote] 把得到的语句贴上来[/quote] 这个 不好意思麻烦了啊 

(15 行受影响)
select?名称,号码,CD?,
?sum(case?日期?when?'1991-01-01'?then?金额?else?0?end)?[1991-01-01]?,
?sum(case?日期?when?'1992-01-01'?then?金额?else?0?end)?[1992-01-01]?,
?sum(case?日期?when?'1993-01-01'?then?金额?else?0?end)?[1993-01-01]?,
?sum(case?日期?when?'1994-01-01'?then?金额?else?0?end)?[1994-01-01]?,
?sum(case?日期?when?'1995-01-01'?then?金额?else?0?end)?[1995-01-01]?,
?sum(case?日期?when?'1996-01-01'?then?金额?else?0?end)?[1996-01-01]?,
?sum(case?日期?when?'1997-01-01'?then?金额?else?0?end)?[1997-01-01]?,
?sum(case?日期?when?'1998-01-01'?then?金额?else?0?end)?[1998-01-01]?,
?sum(case?日期?when?'1999-01-01'?then?金额?else?0?end)?[1999-01-01]?,
?sum(case?日期?when?'2001-01-01'?then?金额?else?0?end)?[2001-01-01]?,
?sum(case?日期?when?'2002-01-01'?then?金额?else?0?end)?[2002-01-01]?,
?sum(case?日期?when?'2003-01-01'?then?金额?else?0?end)?[2003-01-01]?,
?sum(case?日期?when?'2004-01-01'?then?金额?else?0?end)?[2004-01-01]?,
?sum(case?日期?when?'2005-01-01'?then?金额?else?0?end)?[2005-01-01]?,
?sum(case?日期?when?'2006-01-01'?then?金额?else?0?end)?[2006-01-01]?
from?#T?group?by?名称,号码,CD
二月十六 2017-09-13
  • 打赏
  • 举报
 回复
引用 16 楼 weixin_39402825 的回复:
[quote=引用 15 楼 sinat_28984567 的回复:] 没变？在打印一下看看 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
嗯 这次打印的也跟上面的一样没变 中间掺着问号 位置也没变[/quote] 把得到的语句贴上来
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 15 楼 sinat_28984567 的回复:
没变？在打印一下看看 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
嗯 这次打印的也跟上面的一样没变 中间掺着问号 位置也没变
二月十六 2017-09-13
  • 打赏
  • 举报
 回复
没变？在打印一下看看 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
PRINT(@sql)
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 13 楼 sinat_28984567 的回复:
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
EXEC(@sql)
还是一样的错误！
二月十六 2017-09-13
  • 打赏
  • 举报
 回复 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
SET @sql=REPLACE(@sql,'?',' ')
EXEC(@sql)
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 11 楼 sinat_28984567 的回复:
执行这个，把拼接的语句打印出来看看 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
PRINT(@sql) 
SELECT  名称 ,
        号码 ,
        CD ,
        SUM(CASE 日期
              WHEN '1991-01-01' THEN 金额
              ELSE 0
            END) [1991-01-01] ,
        SUM(CASE 日期
              WHEN '1992-01-01' THEN 金额
              ELSE 0
            END) [1992-01-01] ,
        SUM(CASE 日期
              WHEN '1993-01-01' THEN 金额
              ELSE 0
            END) [1993-01-01] ,
        SUM(CASE 日期
              WHEN '1994-01-01' THEN 金额
              ELSE 0
            END) [1994-01-01] ,
        SUM(CASE 日期
              WHEN '1995-01-01' THEN 金额
              ELSE 0
            END) [1995-01-01] ,
        SUM(CASE 日期
              WHEN '1996-01-01' THEN 金额
              ELSE 0
            END) [1996-01-01] ,
        SUM(CASE 日期
              WHEN '1997-01-01' THEN 金额
              ELSE 0
            END) [1997-01-01] ,
        SUM(CASE 日期
              WHEN '1998-01-01' THEN 金额
              ELSE 0
            END) [1998-01-01] ,
        SUM(CASE 日期
              WHEN '1999-01-01' THEN 金额
              ELSE 0
            END) [1999-01-01] ,
        SUM(CASE 日期
              WHEN '2001-01-01' THEN 金额
              ELSE 0
            END) [2001-01-01] ,
        SUM(CASE 日期
              WHEN '2002-01-01' THEN 金额
              ELSE 0
            END) [2002-01-01] ,
        SUM(CASE 日期
              WHEN '2003-01-01' THEN 金额
              ELSE 0
            END) [2003-01-01] ,
        SUM(CASE 日期
              WHEN '2004-01-01' THEN 金额
              ELSE 0
            END) [2004-01-01] ,
        SUM(CASE 日期
              WHEN '2005-01-01' THEN 金额
              ELSE 0
            END) [2005-01-01] ,
        SUM(CASE 日期
              WHEN '2006-01-01' THEN 金额
              ELSE 0
            END) [2006-01-01]
FROM    #T
GROUP BY 名称 ,
        号码 ,
        CD
打出来了 不过跟您的有点不一样 语句里多了很多问号 不好意思啊不是很懂
二月十六 2017-09-13
  • 打赏
  • 举报
 回复
执行这个，把拼接的语句打印出来看看
--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)

Insert #T

select N'a',1,N'A','1991-01-01',500 union all

select N'a',1,N'C','1992-01-01',0 union all

select N'a',3,N'C','1993-01-01',0 union all

select N'a',1,N'A','1994-01-01',400 union all

select N'a',1,N'A','1995-01-01',300 union all

select N'a',2,N'B','1996-01-01',0 union all

select N'b',1,N'A','1997-01-01',0 union all

select N'b',1,N'B','1998-01-01',0 union all

select N'b',1,N'C','1999-01-01',0 union all

select N'b',2,N'A','2001-01-01',0 union all

select N'b',2,N'B','2002-01-01',500 union all

select N'b',2,N'B','2003-01-01',500 union all

select N'b',2,N'C','2004-01-01',0 union all

select N'b',3,N'A','2005-01-01',0 union all

select N'b',3,N'B','2006-01-01',0

Go

--测试数据结束

declare @sql varchar(8000)

set @sql='select 名称,号码,CD'

select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'

from (select distinct 日期 from #T) as a

set @sql=@sql+' from #T group by 名称,号码,CD'

PRINT(@sql) 




SELECT  名称 ,

        号码 ,

        CD ,

        SUM(CASE 日期

              WHEN '1991-01-01' THEN 金额

              ELSE 0

            END) [1991-01-01] ,

        SUM(CASE 日期

              WHEN '1992-01-01' THEN 金额

              ELSE 0

            END) [1992-01-01] ,

        SUM(CASE 日期

              WHEN '1993-01-01' THEN 金额

              ELSE 0

            END) [1993-01-01] ,

        SUM(CASE 日期

              WHEN '1994-01-01' THEN 金额

              ELSE 0

            END) [1994-01-01] ,

        SUM(CASE 日期

              WHEN '1995-01-01' THEN 金额

              ELSE 0

            END) [1995-01-01] ,

        SUM(CASE 日期

              WHEN '1996-01-01' THEN 金额

              ELSE 0

            END) [1996-01-01] ,

        SUM(CASE 日期

              WHEN '1997-01-01' THEN 金额

              ELSE 0

            END) [1997-01-01] ,

        SUM(CASE 日期

              WHEN '1998-01-01' THEN 金额

              ELSE 0

            END) [1998-01-01] ,

        SUM(CASE 日期

              WHEN '1999-01-01' THEN 金额

              ELSE 0

            END) [1999-01-01] ,

        SUM(CASE 日期

              WHEN '2001-01-01' THEN 金额

              ELSE 0

            END) [2001-01-01] ,

        SUM(CASE 日期

              WHEN '2002-01-01' THEN 金额

              ELSE 0

            END) [2002-01-01] ,

        SUM(CASE 日期

              WHEN '2003-01-01' THEN 金额

              ELSE 0

            END) [2003-01-01] ,

        SUM(CASE 日期

              WHEN '2004-01-01' THEN 金额

              ELSE 0

            END) [2004-01-01] ,

        SUM(CASE 日期

              WHEN '2005-01-01' THEN 金额

              ELSE 0

            END) [2005-01-01] ,

        SUM(CASE 日期

              WHEN '2006-01-01' THEN 金额

              ELSE 0

            END) [2006-01-01]

FROM    #T

GROUP BY 名称 ,

        号码 ,

        CD
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 9 楼 weixin_39402825 的回复:
[quote=引用 8 楼 sinat_28984567 的回复:] 
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
exec(@sql)
[quote=引用 6 楼 weixin_39402825 的回复:] [quote=引用 4 楼 sinat_28984567 的回复:] 运行的截图看一下
[/quote] 最上边呢？看着也没输入？，怎么会提示这个呢[/quote] 有啊 没有截到 直接把你的源码复制进去运行的[/quote]
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 8 楼 sinat_28984567 的回复:
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
exec(@sql)
[quote=引用 6 楼 weixin_39402825 的回复:] [quote=引用 4 楼 sinat_28984567 的回复:] 运行的截图看一下
[/quote] 最上边呢？看着也没输入？，怎么会提示这个呢[/quote] 有啊 没有截到 直接把你的源码复制进去运行的
二月十六 2017-09-13
  • 打赏
  • 举报
 回复 
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
exec(@sql)
引用 6 楼 weixin_39402825 的回复:
[quote=引用 4 楼 sinat_28984567 的回复:] 运行的截图看一下
[/quote] 最上边呢？看着也没输入？，怎么会提示这个呢
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 5 楼 zengertao 的回复:
[quote=引用 3 楼 weixin_39402825 的回复:] [quote=引用 2 楼 sinat_28984567 的回复:] 
--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([名称] nvarchar(21),[号码] int,[CD] nvarchar(21),[日期] Date,[金额] int)
Insert #T
select N'a',1,N'A','1991-01-01',500 union all
select N'a',1,N'C','1992-01-01',0 union all
select N'a',3,N'C','1993-01-01',0 union all
select N'a',1,N'A','1994-01-01',400 union all
select N'a',1,N'A','1995-01-01',300 union all
select N'a',2,N'B','1996-01-01',0 union all
select N'b',1,N'A','1997-01-01',0 union all
select N'b',1,N'B','1998-01-01',0 union all
select N'b',1,N'C','1999-01-01',0 union all
select N'b',2,N'A','2001-01-01',0 union all
select N'b',2,N'B','2002-01-01',500 union all
select N'b',2,N'B','2003-01-01',500 union all
select N'b',2,N'C','2004-01-01',0 union all
select N'b',3,N'A','2005-01-01',0 union all
select N'b',3,N'B','2006-01-01',0
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select 名称,号码,CD'
select @sql=@sql+' , sum(case 日期 when '''+ RTRIM(日期) +''' then 金额 else 0 end) ['+ RTRIM(日期) +']'
from (select distinct 日期 from #T) as a
set @sql=@sql+' from #T group by 名称,号码,CD'
exec(@sql)
谢谢大神 ， 复制了你的代码运行时候显示错误 消息 102，级别 15，状态 1，第 1 行 “?”附近有语法错误。 请问是什么原因啊[/quote] 行转列是坛子里的日经贴，随便去已解决模块找找例子，自己消化下就OK了。核心也就是一个动态拼接和行转列语法而已。[/quote] 哦知道了谢谢啊 我是新手最近刚接触sql 很多都不太懂不意思啊
weixin_39402825 2017-09-13
  • 打赏
  • 举报
 回复
引用 4 楼 sinat_28984567 的回复:
运行的截图看一下
加载更多回复（5）
求教大神qbittorrent 如何用nginx反代后域名访问？
求教大神qbittorrent 如何用nginx反代后域名访问？ 我的服务器安装的是4.2.3非docker版，配置好了nginx反代，通过域名+端口访问。但是实际上可以访问，却没办法登录进入，而ip+端口号是正常的。每次输入完用户名密码就跑到地址栏上面去了，然后页面401错误。刷新一下，又是登录界面。一直没弄好，其他应用Tomcat、Mysql、Php都可以反代成功，唯独 qbittorrent 不行。不知道是不是哪里没配置对。希望有大神指导一下。谢谢。 ...
求解一道c语言编程题,大神求教！一道C语言编程题 求教一道c语言编程,大神进!...
导航：网站首页 >大神求教！一道C语言编程题 求教一道c语言编程,大神进!大神求教！一道C语言编程题 求教一道c语言编程,大神进!相关问题:匿名网友:直接上代码#include int main(){double L;double ticheng=0;double rate = 0;int p;printf("请输入利润\n");scanf("%f",&L);if(L <= 1...
求教大神matlab中仿真模拟将数学模型转化为框图形式
小白求教matlab仿真建模中怎样将数学表达式转化为框图
matlab里exist是什么意思,通达信exist函数用法,exist（）函数在matlab中怎么用？求教大神~...
Q1：exist()函数在matlab中怎么用？求教大神~exist name等价于 r=exist(name) ,在程序里面这样更加实用0 不存在则返回值1 name 可以是变量名，如果存在，返回值2 函数名、m 文件名，存在则返回值3 mex 文件、dll 文件，存在则返回值4 内嵌的函数，存在则返回值5 p码文件 ， 存在则返回值6 目录，存在则返回值7 路径，存在则返回值8 Java cl...
求教access中的查询问题
求教access中的查询合计问题
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章