Oracle数据库之行转列与列转行方法

--创建纵表TABLE_A
CREATE TABLE TABLE_A (
    NAME VARCHAR2(20),
    SUBJECT VARCHAR2(20),
    SCORE NUMBER(22,0));

--向表TABLE_A中插入数据
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 60);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 70);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '英语', 80);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '语文', 90);
INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '数学', 100);

插入数据后TABLE_A如下图：

--创建纵表TABLE_B
CREATE TABLE TABLE_B (
    NAME VARCHAR2(20),
    CHINESE NUMBER(22,0),
    MATH NUMBER(22,0),
    ENGLISH NUMBER(22,0));

--向表TABLE_B中插入数据
INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('张三', 60, 70, 80);
INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('李四', 90, 100, 0);

插入数据后TABLE_B如下图：

–纵表转横表 TABLE_A–>TABLE_B

--方法一：使用case ... when ... then ... else ... end  函数
select name,
        sum (case subject when '语文' then score else 0 end) as chinese,
        sum (case subject when '数学' then score else 0 end) as math,
        sum (case subject when '英语' then score else 0 end) as english
        from Table_A
        group by name

--方法二：使用pivot
select * from Table_A pivot (max(score)for subject in('语文','数学','英语'))

–横表转纵表 TABLE_B–>TABLE_A

--方法一：使用union all
select NAME,'语文' as SUBject,chinese as score from Table_B union all
select NAME,'数学' as SUBject,math as score from Table_B union all
select NAME,'英语' as SUBject,english as score from Table_B
order by name,subject desc

--方法二：使用unpivot

select name,subject,score from Table_B
unpivot
(score for subject in (chinese,math,english))