The effect is as shown in the figure:
The test SQL statement is as follows:
Copy Code code 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 (' Class A ', ' John ', ' math ', 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 (' Class A ', ' Dick ', ' math ', 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 ', ' math ', 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 ', ' math ', 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= ' languages ') as ' languages '
, (select IsNull sum (Quantity), 0) from @tab where Class=t.class and student=t.student and course= ' math ') as ' math '
, (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