Column-to-row and row-to-Column

The original data format is as follows:

This is the student's example table. Each subject is a column and must be converted to the following format:

That is, convert the course column to a row and the student row to a column:

Table creation:

Create Table #
(Name varchar (20), English int, Chinese int, math INT)
Insert into # A values ('hangsan', 10, 39, 40)
Insert into # A values ('lisi', 16,25, 36)

 

Train of Thought: first convert columns into rows:

Select name, KM, score
From
(Select name, English, Chinese, math
From # A)
Unregister
(Score for km in
(English, Chinese, math)
)
As unpvt

The following data:

 

Then, convert the row name to a column:

Select *
From
(

Select name, KM, score from

(Select name, KM, score from (Select name, English, Chinese, math from # A) a unordered (score for km in (English, Chinese, math) as unpvt)

)

Bytes
(Max (score) for name in (zhangsan, Lisi ))
As unpvt