一句Sql把纵向表转为横向表，并分别分组求平均和总平均值

declare @tab table(Class varchar(20),Student varchar(20),Course varchar(50),Quantity decimal(7,2));
insert into @tab(Class,Student,Course,Quantity) values(A班,张三,语文,60);
insert into @tab(Class,Student,Course,Quantity) values(A班,张三,数学,70);
insert into @tab(Class,Student,Course,Quantity) values(A班,张三,英语,80);
insert into @tab(Class,Student,Course,Quantity) values(A班,李四,语文,30);
insert into @tab(Class,Student,Course,Quantity) values(A班,李四,数学,40);
insert into @tab(Class,Student,Course,Quantity) values(A班,李四,英语,50);

insert into @tab(Class,Student,Course,Quantity) values(B班,王五,语文,65);
insert into @tab(Class,Student,Course,Quantity) values(B班,王五,数学,75);
insert into @tab(Class,Student,Course,Quantity) values(B班,王五,英语,85);
insert into @tab(Class,Student,Course,Quantity) values(B班,赵六,语文,35);
insert into @tab(Class,Student,Course,Quantity) values(B班,赵六,数学,45);
insert into @tab(Class,Student,Course,Quantity) values(B班,赵六,英语,55);



select * from @tab

select
(case when Grouping(Class)=1 then 总平均 when Grouping(Student)=1 then else Class end ) as Class
,(case when Grouping(Class)=1 then when Grouping(Student)=1 then 平均 else Student end) as Student
,avg(语文) as 语文
,avg(数学) as 数学
,avg(英语) as 英语
,avg(总分) as 总分
from (
select Class,Student
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=语文) as 语文
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=数学) as 数学
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=英语) as 英语
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student) as 总分
from @tab as t
group by Class,Student
) as tempTab
group by Class,Student,语文,数学,英语,总分 with rollup
having Grouping(语文)=1
and Grouping(数学)=1
and Grouping(英语)=1