一个关于SQL的字符串的有关问题

一个关于SQL的字符串的问题

create table facmachine 
(
  facid int ,
  machineid int,
  num int
)
insert into facmachine select 1000,100,10 union all
  select '1000','200',10 union all
  select '2000','100',100 union all
  select '3000', '400',100


select * from facmachine 

declare @sql nvarchar(1000)
set @sql = 'select facid [机型]'
select @sql = @sql+ ','+machineid+'= sum(case machineid when '+[machineid]+' then num else 0 end)' from (select distinct machineid from facmachine) dd
 
set @sql = @sql+', sum(num) as [总计] from facmachine group by facid '
exec (@sql)

drop table facmachine


　　这个Ｓql报＇将 varchar 值 '= sum(case machineid when ' 转换为数据类型为 int 的列时发生语法错误。'这个错误，请教专家怎么解．．．．


[解决办法]
machineid 换成convert(varchar(100),machineid）

SQL code

declare   @sql   nvarchar(1000) set   @sql     =   'select   facid       [机型] '   select   @sql   =  @sql+   ','''+convert(varchar(100),machineid)+ '''= sum(case   machineid   when   '''+convert(varchar(100),machineid)+ '''   then   num   else   0   end) ' from  (select   distinct   machineid   from   facmachine)   dd set   @sql   =   @sql+ ',   sum(num)   as   [总计]   from   facmachine   group   by   facid   ' exec   (@sql)
[解决办法]
SQL code
create   table   facmachine   (     facid   int,     machineid   int,     num   int ) insert   into   facmachine   select   '1000 ', '100 ',10   union   all     select   '1000 ', '200 ',10   union   all     select   '2000 ', '100 ',100   union   all     select   '3000 ',   '400 ',100 declare   @sql   nvarchar(1000) set @sql = 'select facid [机型]' select @sql = @sql+ ', sum(case machineid when ''' + cast(machineid as varchar) + ''' then num else 0 end) ['+cast(machineid as varchar)+']'from (select distinct machineid from facmachine) dd set @sql = @sql+ ', sum(num) as [总计] from facmachine group by facid ' exec   (@sql) /*机型          100         200         400         总计          ----------- ----------- ----------- ----------- ----------- 1000        10          10          0           202000        100         0           0           1003000        0           0           100         100*/
[解决办法]
SQL code
create   table   facmachine   (     facid   int   ,     machineid   int,     num   int ) insert   into   facmachine   select   1000,100,10   union   all     select   '1000 ', '200 ',10   union   all     select   '2000 ', '100 ',100   union   all     select   '3000 ',   '400 ',100 select   *   from   facmachine   declare   @sql   nvarchar(1000) set   @sql     =   'select   facid       [机型] ' select   @sql   =   @sql+   ', ['+cast(machineid as char(20))+ ']=   sum(case   machineid   when   '+cast([machineid] as varchar(20))+ '   then   num   else   0   end) '   from   (select   distinct   machineid   from   facmachine)   dd   set   @sql   =   @sql+ ',   sum(num)   as   [总计]   from   facmachine   group   by   facid   ' exec   (@sql) /*机型          100                  200                  400                  总计          ----------- -------------------- -------------------- -------------------- ----------- 1000        10                   10                   0                    202000        100                  0                    0                    1003000        0                    0                    100                  100*/drop   table   facmachine