SQL 行转列 PIVOT 学习示例

create table [studentscores]
(
[username] nvarchar(20), --学生姓名
[subject] nvarchar(30), --科目
[score] float, --成绩
)

insert into [studentscores] select '张三', '语文', 80
insert into [studentscores] select '张三', '数学', 90
insert into [studentscores] select '张三', '英语', 70
insert into [studentscores] select '张三', '生物', 85
insert into [studentscores] select '李四', '语文', 80
insert into [studentscores] select '李四', '数学', 92
insert into [studentscores] select '李四', '英语', 76
insert into [studentscores] select '李四', '生物', 88
insert into [studentscores] select '码农', '语文', 60
insert into [studentscores] select '码农', '数学', 82
insert into [studentscores] select '码农', '英语', 96
insert into [studentscores] select '码农', '生物', 78


select * from [studentscores]

select * from [studentscores] /*数据源*/
as p 
pivot 
(
sum(score/*行转列后 列的值*/) for 
p.subject/*需要行转列的列*/ in ([语文],[数学],[英语],[生物]/*列的值*/)
) as t where username<>'李四'