SQL Server 行转列的实现

不管我们在平时的学习或工作中，难免会遇到行转列的数据操作，下面的例子可参考一下

1、我们先建表

drop table if exists stu_Score 
create table stu_Score(name varchar(10),course varchar(10),score int)

insert into stu_Score values('Dina','java',82)
insert into stu_Score values('Dina','C#',93)
insert into stu_Score values('Dina','python',90)

insert into stu_Score values('Joyce','java',87)
insert into stu_Score values('Joyce','C#',80)
insert into stu_Score values('Joyce','python',95)

insert into stu_Score values('Mandy','java',93)
insert into stu_Score values('Mandy','C#',86)
insert into stu_Score values('Mandy','python',90)

2、看一下 stu_Score 表 的数据

select * from stu_Score

3、实现数据的行转列

方法一：

select name,
 max(case when course = 'java' then score else null end)java,
 max(case when course = 'C#' then score else null end)C#,
 max(case when course = 'python' then score else null end)python
from stu_Score
group by name

方法二:

select * from stu_Score pivot(max(score)for course in (java, C#, python ))stu_info

两种方法 查询出来的数据都是一样的，可见下图：

 

希望对你有帮助！！！