淺析SQL語句行列轉換的兩種辦法 case...when與pivot函數的運用。本站提示廣大學習愛好者：（淺析SQL語句行列轉換的兩種辦法 case...when與pivot函數的運用）文章只能為提供參考，不一定能成為您想要的結果。以下是淺析SQL語句行列轉換的兩種辦法 case...when與pivot函數的運用正文
/*創立數據庫*/
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
