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

动态行转列问题
有表 #1 ,数据如下:
rkey ,PN ,nums ,rkey1  

215 ,MP4-2642-1 ,30 ,62  
273 ,FE2-2664-1 ,432 ,1  
594 ,FS2-2708-1 ,20 ,370  
632 ,TE4-2718-1 ,48 ,28  
788 ,FE4-1376-1A ,200 ,28  
1466 ,AFE4-1975-1A ,380 ,28  
.......

我要的结果:
rkey ,PN ,62 ,1 ,370 ,28 .....

215 ,MP4-2642-1 ,30 ,0 ,0 ,0  
273 ,FE2-2664-1 ,0 ,432 ,0 ,0
594 ,FS2-2708-1 ,0 ,0 ,20 ,0
632 ,TE4-2718-1 ,0 ,0 ,0 ,48  
788 ,FE4-1376-1A ,0 ,0 ,0 ,200  
1466 ,AFE4-1975-1A ,0 ,0 ,0 ,380  
=================
下面是我写的SQL语句,但总报错: 
[消息 245,级别 16,状态 1,第 4 行
在将 varchar 值 ' then nums else 0 end) as ' 转换成数据类型 int 时失败。]  

declare @sql varchar(8000)
select @sql='select top 100 percent #1.rkey,#1.PN'
select @sql=@sql+
',max(case rkey1 when '+ rkey1 +' then [nums] else 0 end) as '+ convert(varchar(10),rkey1) 
from #1 
 group by #1.rkey1
select @sql=@sql+' from #1 group by #1.rkey,#1.PN,#1.rkey1 '

注意:rkey,nums,rkey1 都是int 型。
 

------解决方案--------------------
SQL code
declare @sql varchar(8000)
select @sql='select top 100 percent #1.rkey,#1.PN'
select @sql=@sql+
',max(case rkey1 when '+ cast(rkey1 as varchar) +' then [nums] else 0 end) as '+ convert(varchar(10),rkey1)  
from #1  
 group by #1.rkey1
select @sql=@sql+' from #1 group by #1.rkey,#1.PN,#1.rkey1 '