需要转化的表结构/以及数据:
使用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