Table transpose in Access and SQL Server

The sample table is as follows:

Student

ID	SName	Subject	score
1	Tom	Chinese	80
2	Tom	Math	90
3	Tom	中文版	85
4	John doe	Chinese	85
5	John doe	Math	92
6	John doe	中文版	82

Access:

TRANSFORM AVG (student.score) as Scoreofavg
SELECT Student.sname
From Student
GROUP by Student.sname
PIVOT Student.subject;

　　

SQL Server:

Select SName,
AVG (case when subject= ' Chinese "then score end) as ' Chinese ',
AVG (case when subject= ' 中文版 ' then score end) as ' 中文版 ',
AVG (case if subject= ' math ' then score end) as ' math '
From Student
Group by SName

SName	Chinese	中文版	Math
John doe	85	82	92
Tom	80	85	90

Upgrade version:

DECLARE @s varchar (8000), @l varchar (8000)
Set @s= "
Select @[email protected] + ', AVG (case Subject when "+ subject+" then score else 0 end) as [' +subject+ '] ' from Student Group by Subject
Set @l= ' Select sName ' [email protected]+ ' from Student Group by SName ORDER by SName '
Exec (@l)