A brief analysis of the two methods of conversion of lines and lines of SQL statement case... The application of when and pivot functions

/* create database */
CREATE DATABASE tmp
go
USE tmp
go

/* create database test tables */
CREATE TABLE [Scores]
(
[ID] INT IDENTITY(1, 1)
PRIMARY KEY ,
[Student] VARCHAR(20) ,
[Subject] VARCHAR(30) ,
[Score] FLOAT
)

go

TRUNCATE TABLE Scores
/* insert database test data information */
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('test001', 'Chinese ', '90')
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('test001', 'English ', '85')
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('text002', 'Chinese ', '90')
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('text002', 'English ', '80')
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('test003', 'Chinese ', '95')
INSERT INTO Scores
( Student, Subject, Score )
VALUES ('test003', 'English ', '85')

/ * 1. case when... then else... end */
SELECT Student AS 'name',
MAX(CASE Subject
WHEN 'language' THEN Score
ELSE 0
END) AS 'language' -- if this row is' language ', select this row as the column
MAX(CASE Subject
WHEN 'English' THEN Score
ELSE 0
END) AS 'English'
FROM Scores
GROUP BY Student
ORDER BY Student

/*2. pivot(aggregate function (column name to be converted to column value)
The column to be converted by for
in (target column name)
) * /

SELECT Student AS 'name',
AVG AS 'Chinese',
AVG(English) AS 'English'
FROM Scores PIVOT(AVG(Score) FOR Subject IN (Chinese, English))as NewScores
GROUP BY Student
ORDER BY Student ASC