How to convert Column records recorded in the database into rows under sqlserver

Assume that there is a student renewal table (tb) as follows:
Name Subject Result
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94
Want to become

Name, Chinese, Mathematics, Physics
-------------------------------------------
Li Si 74 84 94
Zhang San 74 83 93
The SQL statement is as follows:
Copy codeThe Code is as follows:
Create table tb
(
Name varchar (10 ),
Subject varchar (10 ),
Result int
)
Insert into tb (Name, Subject, Result) values ('zhang san', 'China', 74)
Insert into tb (Name, Subject, Result) values ('zhang san', 'mat', 83)
Insert into tb (Name, Subject, Result) values ('zhang san', 'Physical ', 93)
Insert into tb (Name, Subject, Result) values ('Li si', 'China', 74)
Insert into tb (Name, Subject, Result) values ('lily', 'mat', 84)
Insert into tb (Name, Subject, Result) values ('lily', 'Physical ', 94)
Go

-- Static SQL indicates that subject has only three courses: Chinese, mathematics, and physics.
Select name,
Max (case Subject when 'China' then result else 0 end) language,
Max (case Subject when 'mate' then result else 0 end) math,
Max (case Subject when 'physical 'then result else 0 end) Physical
From tb
Group by name

-- Dynamic SQL refers to subject more than three courses: Chinese, mathematics, and physics.

Declare @ SQL varchar (8000)
Set @ SQL = 'select Name as '+ 'name'
Select @ SQL = @ SQL + ', max (case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
From (select distinct Subject from tb) as
Set @ SQL = @ SQL + 'from tb group by name'
Exec (@ SQL)