declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
怎么不支持nvarhcar,怎么办??
------解决方案--------------------
管用吗?它老是提醒 SUM不支持nvarchar
go
Result 列有非数字字符
select * from test where
isnumeric(Result)=0
go
--防止Result有空值
then Result end
改为
then Result else 0 end
------解决方案--------------------
declare @sql varchar(4000)
set @sql = 'select Name '
select @sql = @sql + ',sum(case Subject when ' ' '+Subject+ ' ' ' then Result end) [ '+Subject+ '] '
from (select distinct Subject from CJ) as a
select @sql = @sql+ ' from test group by name '
exec(@sql)
--------------------------------------------
你使用了两个不同的库,CJ,TEST,肯定不行.
另:如果对nvarchar不起作用,可用cast(nvarchar字段 as varchar)即:
case Subject when '''+cast(Subject as varchar)+ '''