/*創建數據庫*/
CREATE DATABASE tmp
go
USE tmp
go
/*創建數據庫測試表*/
CREATE TABLE [Scores]
(
[ID] INT IDENTITY(1, 1)
PRIMARY KEY ,
[Student] VARCHAR(20) ,
[Subject] VARCHAR(30) ,
[Score] FLOAT
)
go
TRUNCATE TABLE Scores
/*插入數據庫測試數據信息*/
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'test001', '語文', '90' )
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'test001', '英語', '85' )
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'text002', '語文', '90' )
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'text002', '英語', '80' )
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'test003', '語文', '95' )
INSERT INTO Scores
( Student, Subject, Score )
VALUES ( 'test003', '英語', '85' )
/*1. case when .......then else ....end 用法，行列轉換*/
SELECT Student AS '姓名' ,
MAX(CASE Subject
WHEN '語文' THEN Score
ELSE 0
END) AS '語文' ,--如果這個行是“語文”，就選此行作為列
MAX(CASE Subject
WHEN '英語' THEN Score
ELSE 0
END) AS '英語'
FROM Scores
GROUP BY Student
ORDER BY Student
/*2. pivot(聚合函數(要轉成列值的列名)
for 要轉換的列
in（目標列名）
)*/
SELECT Student AS '姓名' ,
AVG(語文) AS '語文' ,
AVG(英語) AS '英語'
FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 語文, 英語 ) )as NewScores
GROUP BY Student
ORDER BY Student ASC
