SQL Server Implementation of Row-to-column Conversion

I encountered an interview question recently, and I was confused. I only spoke about my thoughts. Later, Baidu sorted out my thoughts and recorded them to facilitate future study. (For the interview questions, see the attachment)

Related Data Tables:

1. Score Table
 
2. [User] Table
 
The SQL statement is as follows:

-- Method 1: static SQL
Copy codeThe Code is as follows:
SELECT * FROM
(Select uid, Name, Score, ScoreName FROM Score, [User] WHERE Score. UID = [User]. ID) AS SourceTable
Keywords (AVG (Score) FOR ScoreName IN ([English], [mathematics]) AS

-- Method 2: dynamic SQL
Copy codeThe Code is as follows:
DECLARE @ s NVARCHAR (4000)
SELECT @ s = ISNULL (@ s + ',', '') + QUOTENAME (ScoreName)
FROM (select distinct ScoreName from Score) as A --- the column name must not be repeated.

Declare @ SQL NVARCHAR (4000)
SET @ SQL ='
Select r. * from
(Select UID, Name, ScoreName, Score from Score, [User] where Score. UID = [User]. ID) as t
Bytes
(
Max (t. Score)
For t. ScoreName in ('+ @ s + ')
) As R'
EXEC (@ SQL)

-- Method 3: Case When
Copy codeThe Code is as follows:
Select
Row_number () OVER (order by [User]. ID) as number,
UID as user ID,
Name as Name,
Max (case ScoreName when 'then Score else 0 end) English,
Max (case ScoreName when 'mate' then Score else 0 end) math
From Score, [User] WHERE Score. UID = [User]. ID
Group by UID, [User]. ID, Name