Oracl行列转换

1. 行转列

首先创建测试表，并添加测试数据

--行转列测试CREATE  TABLE Scores  (    ID INT PRIMARY KEY,  --主键    StuNo INT,          --学号    Subject VARCHAR2(30),  --科目    Score FLOAT        --成绩) INSERT INTO Scores VALUES(1, 100, '语文', 80);INSERT INTO Scores VALUES(2, 100, '数学', 75);INSERT INTO Scores VALUES(3, 100, '英语', 70);INSERT INTO Scores VALUES(4, 100, '生物', 85);INSERT INTO Scores VALUES(5, 101, '语文', 80);INSERT INTO Scores VALUES(6, 101, '数学', 90);INSERT INTO Scores VALUES(7, 101, '英语', 70);INSERT INTO Scores VALUES(8, 101, '生物', 85);

 

--SQL行转列SELECT StuNo 学号,MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) 语文,MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) 数学,MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) 英语,MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) 生物FROM ScoresGROUP BY StuNoORDER BY StuNo;

 

--PIVOT关键字行转列SELECT StuNo, MAX(语文),  MAX(数学),  MAX(英语),  MAX(生物) FROM (SELECT * FROM ScoresPIVOT(       MAX(Score) FOR Subject IN ('语文' AS "语文", '数学' AS "数学", '英语' AS "英语", '生物' AS "生物"))) GROUP BY StuNo

 

2. 列转行

创建测试所需表和数据

--列转行测试CREATE  TABLEStudentScores  (          ID INT PRIMARY KEY,           --自增标识    StuNo INT,                                          --学号    Chinese VARCHAR2(30),               --语文         Mathematics VARCHAR2(30),     --数学    English VARCHAR2(30),               --英语         Biology VARCHAR2(30)                  --生物) INSERT INTO StudentScores VALUES (1, 100, 80, 85, 75, 80);INSERT INTO StudentScores VALUES (2, 101, 90, 80, 70, 75);INSERT INTO StudentScores VALUES (3, 102, 95, 90, 80, 70);INSERT INTO StudentScores VALUES (4, 103, 60, 70, 80, 85);

 

--SQL列转行SELECT StuNo, 'Chinese' AS Subject, MAX(Chinese) ASScore FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, 'English' AS Subject, MAX(English) ASScore FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, 'Mathematics' AS Subject, MAX(Mathematics) ASScore FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, 'Biology' AS Subject, MAX(Biology) ASScore FROM StudentScores GROUP BY StuNo

 

--UNPIVOT关键字列转行SELECT StuNo, Subject, ScoreFROM StudentScoresUNPIVOT (        Score FORSubject IN (Chinese, English, Mathematics, Biology))