SqlServer:游标+Case 实现行列转化

需要转化的表结构/以及数据:

使用SQl中的Pivot实现

select b.Name,sum(b.地理) 地理,sum(b.英语) 英语,sum(b.语文) 语文,sum(b.数学) 数学 from MCS_TEST.dbo.UserScore a 
pivot(sum(Score) for [Subject] in(数学,语文,英语,地理)) b group by b.Name

使用 游标+Case语句实现:

declare @sql nvarchar(max),@subject nvarchar(20)
declare autopivot cursor for select distinct([Subject]) from dbo.UserScore
open autopivot
	set @sql='select Name'
	fetch next from autopivot into @subject
	while @@fetch_status=0
	begin
		--print @subject
		set @sql+=',sum(case [Subject] when '''+@subject+''' then Score else 0 end) '+@subject+''
		fetch next from autopivot into @subject
	end
	set @sql+=' from UserScore group by Name'
	print @sql
	exec(@sql)
close autopivot
deallocate autopivot

定义的@sql最终的结果:
 

select Name,sum(case [Subject] when '地理' then Score else 0 end) 地理,sum(case [Subject] when '数学' then Score else 0 end) 数学,sum(case [Subject] when '英语' then Score else 0 end) 英语,sum(case [Subject] when '语文' then Score else 0 end) 语文 from UserScore group by Name

游标+pivot:

declare @subject nvarchar(20),@sql nvarchar(max),@subjects nvarchar(4000),@len int,@fields nvarchar(2000)
declare curunpivot cursor for select distinct([Subject]) from dbo.UserScore
open curunpivot
	fetch next from curunpivot into @subject
	set @sql=''
	set @subjects=''
	set @fields=''
	while @@fetch_status =0 
	begin
		set @len=len(@subjects)
		if(@len=0)
		begin
			set @subjects+=@subject
		end
		else
		begin
			set @subjects+=(','+@subject)
		end
		set @len=len(@fields)
		if(@len=0)
		begin
			set @fields+=('sum(a.'+@subject+') '+@subject)
		end
		else
		begin
			set @fields+=(',sum(a.'+@subject+') '+@subject)
		end
		print @subjects
		print @fields
		fetch next from curunpivot into @subject
	end
	set @sql+='select Name,'+@fields+' from UserScore a pivot (sum(Score) for [Subject] in('+@subjects+'))a group by Name'
	print @sql
	exec(@sql)
close curunpivot
deallocate curunpivot