SQL row and column career change

Row to column, column change is the problem we often encounter in the development process. Row-columns are typically implemented by the case-when statement, or by SQL SERVER 2005 's new operator pivot. In the traditional way, better understanding. The level is clear, and more accustomed. But pivot, UNPIVOT provides more syntax than a series of complex select ... The syntax specified in the case statement is simpler and more readable. Here are a few simple examples to introduce the column-change, row-and-column issues.

Let's start with a cliché example, a student's score sheet (simplified below) to figure out the downward row

CREATE  TABLE [studentscores]
   
(
   
   [UserName]         NVARCHAR,        --Student name
   
    [Subject]          NVARCHAR (        --Subject
   
    [Score]            FLOAT,               --score
   
) INSERT into
   
    
   
[studentscores] SELECT ' Nick ', ' language ', 80
   
    
   
INSERT into [studentscores] SELECT ' Nick ', ' math ',
   
    
   
insert INTO [studentscores] select ' Nick ', ' English ',
   
    
   
insert INTO [studentscores] select ' Nick ', ' creature ',
   
    
   
insert I NTO [studentscores] Select ' Kent ', ' language ',
   
    
   
insert INTO [studentscores] select ' Kent ', ' Math ', and
   
    
   
insert into [Stu Dentscores] Select ' Kent ', ' English ',
   
    
   
INSERT into [studentscores] select ' Kent ', ' creature ', 85

If I want to know each student's grade, and each student's score line, so that I see, statistics, export data

SELECT
   
      UserName, 
   
      Max (case Subject when ' language ' THEN Score ELSE 0 end) as ' language ',
   
      max (case Subject when ' math ' THEN Sc Ore ELSE 0 end) as ' math ',
   
      max (case Subject when ' English ' THEN Score ELSE 0 end) as ' English ',
   
      max (case Subject ' creature ') N Score ELSE 0 end) as ' creature ' from
   
dbo.[ Studentscores]
   
GROUP by UserName

The results of the query are shown in the figure, so that we can get a clear picture of each student's performance.