这是一个简单的例子 你可以看看 create table temp ( Name varchar(20) not null,--学生 id int,--课程编号 score int --成绩 ) go insert into temp select'peter',1,88 union all select 'peter',2,75 union all select'peter',3,80 union all select'jimmy',1,44 union all select'jimmy',2,88 union all select'jimmy',3,100 union all select'aiden',1,85 union all select'aiden',3,90 goselect * from temp pivot (max(score) for name in([peter],[jimmy],[aiden]))a
if not object_id('Class') is null drop table Class Go Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int) Insert Class select N'张三',N'语文',78 union all select N'张三',N'数学',87 union all select N'张三',N'英语',82 union all select N'张三',N'物理',90 union all select N'李四',N'语文',65 union all select N'李四',N'数学',77 union all select N'李四',N'英语',65 union all select N'李四',N'物理',85 Goselect [Student],[数学],[物理],[英语],[语文],[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score]) pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b /* Student 数学 物理 英语 语文 总成绩 ------- ----------- ----------- ----------- ----------- ----------- 李四 77 85 65 65 292 张三 87 90 82 78 337
create table temp
(
Name varchar(20) not null,--学生
id int,--课程编号
score int --成绩
)
go
insert into temp
select'peter',1,88 union all
select 'peter',2,75 union all
select'peter',3,80 union all
select'jimmy',1,44 union all
select'jimmy',2,88 union all
select'jimmy',3,100 union all
select'aiden',1,85 union all
select'aiden',3,90
goselect * from temp
pivot
(max(score) for name in([peter],[jimmy],[aiden]))a
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Goselect
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b /*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/