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 '''[email protected]+''' then Score else 0 end) '[email protected]+''
		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