行转列
if not object_id('Class') is null
drop table ClassGo
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'语文',95 union all
select N'李四',N'数学',77 union all
select N'李四',N'数学',79 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
select * from Class
select * from class pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
select [Student],[数学],[物理],[英语],[语文],[总成绩] from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
列转行
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
Select * from class
select Student,[Course],[Score] from Class
unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b