SqlServer 行转列

 

表结构与数据

 

SELECT name,
 max(CASE kecheng WHEN'语文' THEN fenshu ELSE 0 END) 语文,
 max(CASE kecheng WHEN'数学' THEN fenshu ELSE 0 END) 数学,
 max(CASE kecheng WHEN'英语' THEN fenshu ELSE 0 END) 英语
FROM student
GROUP BY name

--动态sql
declare @sql varchar(500)
set @sql=' select name'
select @sql = @sql +',max(case kecheng when '''+kecheng+''' then fenshu else 0 end)['+kecheng+']'
from (select distinct kecheng from student) a --同from tb group by课程，默认按课程名排序
set @sql=@sql+' from student group by name'
exec(@sql)


--使用isnull(),变量先确定动态部分
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+' max(case kecheng when '''+kecheng+''' then fenshu else 0 end) ['+kecheng+']'
from( select distinct kecheng from student) asa      
set @sql=' select name,'+@sql+' from student group by name'
exec(@sql)

--这种形式，网上提供的，我测试没有成功，暂时没有找到原因
SELECT name,语文,数学,英语 FROM student pivot( max(fenshu) FOR kecheng IN (语文,数学,英语))a