sqlserver 行转列

--行转列 case when then else 
select name,
max(case course when '语文' then score else 0 end) 语文,
max(case course when '数学' then score else 0 end) 数学
from [dbo].[test_pivot]
group by name
--PIVOT函数实现
SELECT *
FROM test_pivot
PIVOT (max(score) FOR course IN (语文,数学))
as t

--动态方式实现
declare @sql varchar(4000)
set @sql='select name'
select @sql=@sql+', max(case course when '''+course+''' then score else 0 end) '+course+''
from (select distinct course from test_pivot) as a
order by course
select @sql=@sql+' from  test_pivot  group by NAME'
print @sql
exec (@sql)

--PIVOT函数动态实现

declare  @name nvarchar(2000),
@name1 nvarchar(2000),
 @strSql nvarchar(2000)
 
SET @name=''
 --赋值 把所有要转化为列的数据保存在字符串中，并且以逗号分隔
 select  @name=@name+CDate  from (select  distinct '['+course+'],' AS CDate FROM test_pivot) AS T1
SET @name1=''
 
 select  @name1=@name1+CDate  from (select  distinct 'max('+course+') as '+course+',' AS CDate FROM test_pivot) AS T2
 SET @name1=SUBSTRING(@name1,1,LEN(@name1)-1)
 --去掉末尾的一个逗号
 SET @name=SUBSTRING(@name,1,LEN(@name)-1)
  PRINT(@name)
   PRINT(@name1)
   SET  @strSql ='select b.name as 姓名,'+@name1+'
    from test_pivot 
    pivot(
    max(score) 
    for course 
    in ('+@Name+')
    )  
    as b
    group by b.name'
--打印最终执行的SQL
 --PRINT(@strSql)
 exec(@strsql)

原始表数据