One SQL turns the vertical table into a horizontal table and groups the average and the total mean separately.

The effect is as shown in the figure:

The test SQL statement is as follows:
Copy CodeThe code is as follows:
Declare @tab table (Class varchar, Student varchar, Course varchar), Quantity (7,2));
Insert into @tab (class,student,course,quantity) VALUES ("Class A", "John", "language", 60);
Insert into @tab (class,student,course,quantity) VALUES ("A Class", "John", "Mathematics", 70);
Insert into @tab (class,student,course,quantity) VALUES ("Class A", "John", "English", 80);
Insert into @tab (class,student,course,quantity) VALUES ("Class A", "Dick", "language", 30);
Insert into @tab (class,student,course,quantity) VALUES ("A Class", "Dick", "mathematics", 40);
Insert into @tab (class,student,course,quantity) VALUES ("Class A", "Dick", "English", 50);

Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Harry", "language", 65);
Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Harry", "mathematics", 75);
Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Harry", "English", 85);
Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Zhao Liu", "language", 35);
Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Zhao Liu", "mathematics", 45);
Insert into @tab (class,student,course,quantity) VALUES ("Class B", "Zhao Liu", "English", 55);



SELECT * FROM @tab

Select
(Case when Grouping (Class) =1 then "total average" when Grouping (Student) =1 then "" Else class end) as Class
, (case where Grouping (Class) =1 then "when Grouping (Student) =1 then" average "else Student end) as Student
, AVG (Chinese) as language
, AVG (mathematics) as Mathematics
, AVG (English) as English
, AVG (total) as total score
From (
Select Class,student
, (select IsNull sum (Quantity), 0) from @tab where Class=t.class and Student=t.student and course= "language") as "language"
, (select IsNull (sum (Quantity), 0) from @tab where Class=t.class and Student=t.student and course= "mathematics") as "mathematics"
, (select IsNull sum (Quantity), 0) from @tab where Class=t.class and Student=t.student and course= "English") as "English"
, (select IsNull (sum (Quantity), 0) from @tab where Class=t.class and student=t.student) as "Total Score"
From @tab as T
GROUP BY Class,student
) as Temptab
GROUP by Class,student, Chinese, maths, English, total score with rollup
Having Grouping (Chinese) =1
and Grouping (mathematics) =1
and Grouping (English) =1