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

淩亂°似流年 2021-06-26 16:06 528阅读 0赞

--创建纵表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`如下图：  
![在这里插入图片描述][20190506232601464.png]

--创建纵表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`如下图：  
![在这里插入图片描述][20190506232709143.png]  
**–纵表转横表 `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))

[20190506232601464.png]: /images/20210626/975b4c1a455b4899b318c1f13ac80f78.png
[20190506232709143.png]: https://img-blog.csdnimg.cn/20190506232709143.png