Oracle行转列pivot

最新推荐文章于 2024-07-05 16:29:50 发布

小百菜

最新推荐文章于 2024-07-05 16:29:50 发布

阅读量2k

点赞数

文章标签： oracle 数据库 pivot 行转列

本文链接：https://blog.csdn.net/u014644574/article/details/128302489

版权

案例一：

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

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

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

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'))