SQL Server row to column

Interview encountered a recent interview problem, suddenly a little confused, only to say the idea, then Baidu a bit, collated a bit of ideas, so record down, easy to learn later. (see attachment for questions)

Related Data sheets:

1.Score table

2.[user] Table

The SQL statements are as follows:

--Method one: Static SQL
SELECT * FROM
(SELECT uid,name, score,scorename from Score,[user] WHERE score.uid=[user].id) As SourceTable
PIVOT (AVG (score) for Scorename in ([English], [math])) as a

--Method two: Dynamic SQL
DECLARE @s NVARCHAR (4000)
SELECT @s = ISNULL (@s + ', ', ') + QUOTENAME (scorename)
From (select distinct scorename from score) as A---column names do not repeat

Declare @sql NVARCHAR (4000)
SET @sql = '
Select r.* from
(select Uid,name,scorename,score from Score,[user] where score.uid=[user].id) as T
Pivot
(
Max (T.score)
For T.scorename in (' +@s+ ')
) as R '
EXEC (@sql)

--Method Three: case when
Select
Row_number () over (ORDER by [user].id) as number,
UID as user number,
Name as names,
Max (case scorename when ' English ' then score else 0 end) English,
Max (case scorename when ' math ' then score else 0 end) Math
From Score,[user] WHERE score.uid=[user].id
GROUP BY Uid,[user].id,name