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

效果如图所示:

测试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