sql 行转列

--create Table init Data   
create table students (  
name varchar(25),
class varchar(25),   
  grade int   )   
insert into students values ('张三','语文',20)   
insert into students values ('张三','数学',90)   
insert into students values ('张三','英语',50)   
insert into students values ('李四','语文',81)   
insert into students values ('李四','数学',60)   
insert into students values ('李四','英语',90)   
 
-- solution1
select * from students
pivot(
max(grade)
FOR [class] IN ([语文],[数学],[英语])
) AS pvt

DECLARE @strCN NVARCHAR(500);
 SELECT @strCN=ISNULL(@strCN+',','')+ QUOTENAME(class) FROM students GROUP BY [class]; --PRINT @strCN
 DECLARE @SqlStr NVARCHAR(500);
 SET @SqlStr='SELECT * FROM Students PIVOT(MAX(grade) FOR [class]
  IN('+@strCN+')) as pvt';
   EXEC(@sqlstr)