SQL vertical table to horizontal table

Declare @ tab table (class varchar (20), student varchar (20), course varchar (50), grades decimal (7, 2 ));
Insert into @ tab (class, student, course, grades) values ('class A ', 'zhang san', 'China', 60 );
Insert into @ tab (class, student, course, grades) values ('class A ', 'zhang san', 'mat', 70 );
Insert into @ tab (class, student, course, grades) values ('class A ', 'zhang san', 'English', 80 );
Insert into @ tab (class, student, course, grades) values ('class', 'lily', 'China', 30 );
Insert into @ tab (class, student, course, grades) values ('class', 'lily', 'mat', 40 );
Insert into @ tab (class, student, course, grades) values ('class A ', 'Li si', 'English', 50 );
Insert into @ tab (class, student, course, grades) values ('B class', 'wang 5', 'China', 65 );
Insert into @ tab (class, student, course, grades) values ('B class', 'wang 5', 'mat', 75 );
Insert into @ tab (class, student, course, grades) values ('B ban', 'wang wu', 'English', 85 );
Insert into @ tab (class, student, course, grades) values ('B class', 'zhao liu', 'China', 35 );
Insert into @ tab (class, student, course, grades) values ('B ban', 'zhao liu', 'mat', 45 );
Insert into @ tab (class, student, course, grades) 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
, Cast (AVG (language) as decimal () as language
, Cast (AVG (Mathematics) as decimal (7, 2) as mathematics
, Cast (AVG (English) as decimal (7,2) as English
, Cast (AVG (total) as decimal (7, 2) as total score
From (
Select class, student
, (Select isnull (sum (grades), 0) from @ tab where class = "T". Class and student = T. Student and course = 'China') as 'China'
, (Select isnull (sum (grades), 0) from @ tab where class = "T". Class and student = T. Student and course = 'mat') as 'mat'
, (Select isnull (sum (grades), 0) from @ tab where class = "T". Class and student = T. Student and course = 'inc') as 'inc'
, (Select isnull (sum (grades), 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

SQL statement-regular row-column Conversion

Suppose there is a student summary table (result) as follows:
Name subject result
Zhang San Chinese 73
James math 83
Zhang San
Physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94

Table creation:

Create Table Table1 (name varchar (50), subject varchar (50), result INT)

Insert into Table1 values ('zhang san', 'China', '73 ')
Insert into Table1
Values ('zhang san', 'mat', '83 ')
Insert into Table1 values ('zhang san', 'Physical ', '93 ')
Insert
Into Table1 values ('Li si', 'China', '74 ')
Insert into Table1
Values ('Li si', 'mat', '84 ')
Insert into Table1 values ('lily', 'Physical ', '94 ')

Want to become
Name, Chinese, Mathematics, Physics
Zhang San 73 83 93
Li Si 74 84 94

Declare @ SQL varchar (4000)
Set @ SQL = 'select name as '+ 'name'
Select
@ SQL = @ SQL + ', sum (case subject when ''' + Subject + ''' then result end)
['+ Subject +']'
From (select distinct subject from Table1) as
Set @ SQL =
@ SQL + 'from Table1 group by name'
Exec (@ SQL)

If the two tables change each other:
Table Name (CJ)
Name, Chinese, Mathematics, Physics
Zhang San 73 83 93
Li Si 74 84 94

Want to become

Name subject result
Zhang San Chinese 73
James math 83
Zhang San physical 93
Li Si language 74
Li Si
Mathematics 84
Li Si physical 94

Select name as name, 'China' as subject, language as result from CJ Union
Select name
Name, 'mate' as subject, mathematics as result from CJ Union
Select name as name, 'Physical'
Subject, physical as result from CJ
Order by name DESC