Sqlselect-row-column Conversion

Normal row/column Conversion

Suppose there is a student renewal table (T) 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

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

Create Table # T
(
Name varchar (10 ),
Subject varchar (10 ),
Result int
)

Insert into # T (name, subject, result) values ('zhang san', 'China', '73 ')
Insert into # T (name, subject, result) values ('zhang san', 'mat', '83 ')
Insert into # T (name, subject, result) values ('zhang san', 'Physical ', '93 ')
Insert into # T (name, subject, result) values ('Li si', 'China', '74 ')
Insert into # T (name, subject, result) values ('lily', 'mat', '83 ')
Insert into # T (name, subject, result) values ('lily', 'Physical ', '93 ')

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

Drop table # T

-- Result
Name, mathematics, physics, and Chinese
-------------------------------------------
Li Si 83 93 74
Zhang San 83 93 73
 
----------------------------------------------------
If the two tables change each other:

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

Create Table # T
(
Name varchar (10 ),
Chinese int,
Mathematical int,
Physical int
)

Insert into # T (name, Chinese, Mathematics, Physics) values ('zhang san', 93)
Insert into # T (name, Chinese, mathematics, and physics) values ('Li si', 94)

Select name as name, 'China' as subject, language as result from # T Union
Select name as name, 'mate' as subject, mathematics as result from # T Union
Select name as name, 'Physical 'as subject, physical as result from # T
Order by name DESC

Drop table # T
 
-- Result
Name subject result
----------------------------
James math 83
Zhang San physical 93
Zhang San Chinese 73
Li Si mathematics 84
Li Si physical 94
Li Si language 74

(The number of affected rows is 6)