Oracle行转列pivot

╰+攻爆jí腚メ 2023-10-07 22:04 1阅读 0赞

## 案例一： ##

按天汇总学生每天学习时长

create table stu
    (
      day  VARCHAR2(20),
      name VARCHAR2(20),
      len  NUMBER
    );
    INSERT INTO stu VALUES ('01', '张三', 3);
    INSERT INTO stu VALUES ('01', '李四', 4);
    INSERT INTO stu VALUES ('02', '张三', 1);
    INSERT INTO stu VALUES ('02', '李四', 5);
    INSERT INTO stu VALUES ('03', '张三', 8);
    INSERT INTO stu VALUES ('03', '李四', 6);
    INSERT INTO stu VALUES ('04', '张三', 3);
    INSERT INTO stu VALUES ('04', '李四', 2);
    INSERT INTO stu VALUES ('05', '张三', 2);
    INSERT INTO stu VALUES ('05', '李四', 5);
    INSERT INTO stu VALUES ('06', '张三', 7);
    INSERT INTO stu VALUES ('06', '李四', 2);
    INSERT INTO stu VALUES ('07', '张三', 2);
    INSERT INTO stu VALUES ('07', '李四', 3);
    INSERT INTO stu VALUES ('08', '张三', 1);
    INSERT INTO stu VALUES ('08', '李四', 3);
    INSERT INTO stu VALUES ('09', '张三', 5);
    INSERT INTO stu VALUES ('09', '李四', 1);
    INSERT INTO stu VALUES ('10', '张三', 6);
    INSERT INTO stu VALUES ('10', '李四', 7);

select day,name,len from stu

![bfb60f05541f4daa8b69c7c97501a173.png][]

![1cd630b5e8ab43b6a9bffa35c18d43f2.png][]

select *
      from (SELECT name, len, day FROM stu) t
    pivot(max(len)
       for day in('01',
                  '02',
                  '03',
                  '04',
                  '05',
                  '06',
                  '07',
                  '08',
                  '09',
                  '10',
                  '11',
                  '12',
                  '13',
                  '14',
                  '15'))

## 案例二： ##

按天汇总学生每天学习时长(分上下午汇总)

create table stu
    (
      day  VARCHAR2(20),
      name VARCHAR2(20),
      len  NUMBER
    );
    INSERT INTO stu VALUES ('01', '张三', 1);--上午
    INSERT INTO stu VALUES ('01', '张三', 2);--下午
    INSERT INTO stu VALUES ('01', '李四', 2);--上午
    INSERT INTO stu VALUES ('01', '李四', 2);--下午
    INSERT INTO stu VALUES ('02', '张三', 1);
    INSERT INTO stu VALUES ('02', '李四', 3);
    INSERT INTO stu VALUES ('02', '李四', 2);
    INSERT INTO stu VALUES ('03', '张三', 5);
    INSERT INTO stu VALUES ('03', '张三', 3);
    INSERT INTO stu VALUES ('03', '李四', 2);
    INSERT INTO stu VALUES ('03', '李四', 4);
    INSERT INTO stu VALUES ('04', '张三', 3);
    INSERT INTO stu VALUES ('04', '李四', 2);
    INSERT INTO stu VALUES ('05', '张三', 2);
    INSERT INTO stu VALUES ('05', '李四', 5);
    INSERT INTO stu VALUES ('06', '张三', 7);
    INSERT INTO stu VALUES ('06', '李四', 2);
    INSERT INTO stu VALUES ('07', '张三', 2);
    INSERT INTO stu VALUES ('07', '李四', 3);
    INSERT INTO stu VALUES ('08', '张三', 1);
    INSERT INTO stu VALUES ('08', '李四', 3);
    INSERT INTO stu VALUES ('09', '张三', 5);
    INSERT INTO stu VALUES ('09', '李四', 1);
    INSERT INTO stu VALUES ('10', '张三', 6);
    INSERT INTO stu VALUES ('10', '李四', 7);

select day,name,len from stu

![5b7a264dff3a44bbbae7aa9a9431f0c3.png][]

![45c86612db1048cf8accf66f9d13d384.png][]

select *
      from (SELECT name, len, day FROM stu) t
    pivot(sum(len)
       for day in('01',
                  '02',
                  '03',
                  '04',
                  '05',
                  '06',
                  '07',
                  '08',
                  '09',
                  '10',
                  '11',
                  '12',
                  '13',
                  '14',
                  '15'))

[bfb60f05541f4daa8b69c7c97501a173.png]: https://img-blog.csdnimg.cn/bfb60f05541f4daa8b69c7c97501a173.png
[1cd630b5e8ab43b6a9bffa35c18d43f2.png]: https://img-blog.csdnimg.cn/1cd630b5e8ab43b6a9bffa35c18d43f2.png
[5b7a264dff3a44bbbae7aa9a9431f0c3.png]: https://img-blog.csdnimg.cn/5b7a264dff3a44bbbae7aa9a9431f0c3.png
[45c86612db1048cf8accf66f9d13d384.png]: https://img-blog.csdnimg.cn/45c86612db1048cf8accf66f9d13d384.png