/* 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