select [姓名], [英语]=max(case when [科目]='英语' then [成绩] else 0 end), [数学]=max(case when [科目]='数学' then [成绩] else 0 end), [语文]=max(case when [科目]='语文' then [成绩] else 0 end) from tb group by [姓名]
if not object_id('[tb]') is null drop table tb create table tb(姓名 varchar(5),科目 varchar(10),成绩 int) insert into tb select '张三','英语',90 union all select '李四','英语',85 union all select '王五','英语',80 union all select '张三','数学',95 union all select '李四','数学',92 union all select '王五','数学',98 union all select '张三','语文',80 union all select '李四','语文',85 union all select '王五','语文',82select [姓名], [英语]=max(case when [科目]='英语' then [成绩] else 0 end), [数学]=max(case when [科目]='数学' then [成绩] else 0 end), [语文]=max(case when [科目]='语文' then [成绩] else 0 end) from tb group by [姓名]------查询结果-------- 李四 85 92 85 王五 80 98 82 张三 90 95 80
[英语]=max(case when [科目]='英语' then [成绩] else 0 end),
[数学]=max(case when [科目]='数学' then [成绩] else 0 end),
[语文]=max(case when [科目]='语文' then [成绩] else 0 end)
from tb
group by [姓名]
drop table tb
create table tb(姓名 varchar(5),科目 varchar(10),成绩 int)
insert into tb select '张三','英语',90
union all select '李四','英语',85
union all select '王五','英语',80
union all select '张三','数学',95
union all select '李四','数学',92
union all select '王五','数学',98
union all select '张三','语文',80
union all select '李四','语文',85
union all select '王五','语文',82select [姓名],
[英语]=max(case when [科目]='英语' then [成绩] else 0 end),
[数学]=max(case when [科目]='数学' then [成绩] else 0 end),
[语文]=max(case when [科目]='语文' then [成绩] else 0 end)
from tb
group by [姓名]------查询结果--------
李四 85 92 85
王五 80 98 82
张三 90 95 80