行列互换

行转列

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'语文',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