行转列和列转行同时用的一个例子

刚才看了一个帖子，觉得那个问题会经常遇到，就随手写下来吧。

代码：

CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), --学生姓名 
[china] int, --科目 
[数学]int
 ) 
INSERT INTO [StudentScores] SELECT 'Nick', 70, 80 INSERT INTO [StudentScores] SELECT 'Nick1', 80, 90 INSERT INTO [StudentScores] SELECT 'jick', 50, 70 INSERT INTO [StudentScores] SELECT 'lucy', 70, 85 INSERT INTO [StudentScores] SELECT 'Kent', 80, 80 INSERT INTO [StudentScores] SELECT 'anne', 90, 90 INSERT INTO [StudentScores] SELECT 'lili',75, 70 INSERT INTO [StudentScores] SELECT 'jnne', 55, 85
select * from studentscores

结果：

想要的结果是：