Sql分组查询学生平均成绩和行专列列转行

--创建一个学生表
create table Stu
(
    ID int primary key identity,
	Name varchar(50),
	Course varchar(50),
	Score int
)



--分组查询平均成绩
select Name 姓名,Course 科目,Avg(Score) 平均成绩 from Stu Group By Name,Course

--行专列查询平均成绩
select Name 姓名,Avg(语文) as 语文,avg(数学) as 数学, avg(物理) as 物理 from Stu
pivot (avg(Score) for Course in (语文,数学,物理)) as T group by Name

--创建一个视图
alter view V_Stu
as
select Name 姓名,Avg(语文) as 语文,avg(数学) as 数学, avg(物理) as 物理 from Stu
pivot (avg(Score) for Course in (语文,数学,物理)) as T group by Name


--列转行
select * from V_Stu unpivot (Score for Course in (语文,数学,物理)) as T