一、原数据表

name    course    score
张三    A    90
张三    B    90
张三    C    90
张三    D    90
张三    E    90
张三    F    90
张三    G    90
李四    A    90
李四    B    90
李四    C    90
李四    D    90
李四    E    90
李四    H    90
李四    I    90

二、行转列

2.1case when 动态行转列

declare @sql varchar(4000)  
set @sql = 'select NAME'  
  
select @sql = @sql + ',sum(case COURSE when '''+COURSE+''' then  SCORE end) ['+COURSE+']'  
from (select distinct COURSE from STUDENT  ) as a  
order by COURSE  
   
select @sql = @sql+' from STUDENT  group by NAME'  
  
-- print @sql  
exec(@sql) 

2.2 case when 静态行转列

select NAME,  
sum(case COURSE when 'A' then SCORE end) as A,  
sum(case COURSE when 'B' then SCORE end) as B,
sum(case COURSE when 'C' then SCORE end) as C,
sum(case COURSE when 'D' then SCORE end) as D,
sum(case COURSE when 'E' then SCORE end) as E,
sum(case COURSE when 'F' then SCORE end) as F,
sum(case COURSE when 'G' then SCORE end) as G,
sum(case COURSE when 'H' then SCORE end) as H
from student  
-- where   
group by NAME  

2.3 内置函数pivot

SELECT *
FROM student
PIVOT (SUM(score) FOR course IN (A,B,C,D,E,F,G,H,I) ) 
as t