An SQL statement converts a vertical table into a horizontal table, and groups them to obtain the average and total average values.

Effect:

The test SQL statement is as follows:
Copy codeThe Code is as follows:
Declare @ tab table (Class varchar (20), Student varchar (20), Course varchar (50), Quantity decimal (7, 2 ));
Insert into @ tab (Class, Student, Course, Quantity) values ('class A ', 'zhang san', 'China', 60 );
Insert into @ tab (Class, Student, Course, Quantity) values ('class A ', 'zhang san', 'mat', 70 );
Insert into @ tab (Class, Student, Course, Quantity) values ('class A ', 'zhang san', 'English', 80 );
Insert into @ tab (Class, Student, Course, Quantity) values ('class A ', 'Li si', 'China', 30 );
Insert into @ tab (Class, Student, Course, Quantity) values ('class', 'lily', 'mat', 40 );
Insert into @ tab (Class, Student, Course, Quantity) values ('class A ', 'Li si', 'English', 50 );

Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'wang 5', 'China', 65 );
Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'wang 5', 'mat', 75 );
Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'wang wu', 'English', 85 );
Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'zhao liu', 'China', 35 );
Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'zhao liu', 'mat', 45 );
Insert into @ tab (Class, Student, Course, Quantity) values ('B ban', 'zhao liu', 'English', 55 );



Select * from @ tab

Select
(Case when Grouping (Class) = 1 then' total averages 'when Grouping (Student) = 1 then'' else Class end) as Class
, (Case when 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 score) as total score
From (
Select Class, Student
, (Select isnull (sum (Quantity), 0) from @ tab where Class = t. Class and Student = t. Student and Course = 'China') as 'China'
, (Select isnull (sum (Quantity), 0) from @ tab where Class = t. Class and Student = t. Student and Course = 'mate') as 'mat'
, (Select isnull (sum (Quantity), 0) from @ tab where Class = t. Class and Student = t. Student and Course = 'inc') as 'inc'
, (Select isnull (sum (Quantity), 0) from @ tab where Class = t. Class and Student = t. Student) as 'Total'
From @ tab as t
Group by Class, Student
) As tempTab
Group by Class, Student, Chinese, mathematics, English, total score with rollup
Having Grouping (Chinese) = 1
And Grouping (Mathematics) = 1
And Grouping (English) = 1