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 Scores

GROUP BY StuNo

ORDER BY StuNo;

 

--PIVOT关键字行转列

SELECT StuNo, MAX(语文),  MAX(数学),  MAX(英语),  MAX(生物) FROM (

SELECT * FROM Scores

PIVOT(

       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 StuNo

UNION ALL

SELECT StuNo, 'English' AS Subject, MAX(English) ASScore FROM StudentScores GROUP BY StuNo

UNION ALL

SELECT StuNo, 'Mathematics' AS Subject, MAX(Mathematics) ASScore FROM StudentScores GROUP BY StuNo

UNION ALL

SELECT StuNo, 'Biology' AS Subject, MAX(Biology) ASScore FROM StudentScores GROUP BY StuNo

 

--UNPIVOT关键字列转行

SELECT StuNo, Subject, Score

FROM StudentScores

UNPIVOT (

        Score FORSubject IN (Chinese, English, Mathematics, Biology)

)