度量快速开发平台-专业、快速的软件定制快开平台

标题: oracle行列转换多种方式介绍 [打印本页]
作者: 张兴康    时间: 2020-3-9 19:18
作者: caixuqad    时间: 2020-3-10 17:33
作者: 张兴康    时间: 2020-7-26 18:26
标题: oracle行列转换多种方式介绍
1、建表
  1. 1create table kecheng
  2. 2 (
  3. 3   id     NUMBER,
  4. 4   name   VARCHAR2(20),
  5. 5   course VARCHAR2(20),
  6. 6   score  NUMBER
  7. 7 );
  8. 8 insert into kecheng (id, name, course, score)
  9. 9 values (1, '张三', '语文', 67);
  10. 10 insert into kecheng (id, name, course, score)
  11. 11 values (1, '张三', '数学', 76);
  12. 12 insert into kecheng (id, name, course, score)
  13. 13 values (1, '张三', '英语', 43);
  14. 14 insert into kecheng (id, name, course, score)
  15. 15 values (1, '张三', '历史', 56);
  16. 16 insert into kecheng (id, name, course, score)
  17. 17 values (1, '张三', '化学', 11);
  18. 18 insert into kecheng (id, name, course, score)
  19. 19 values (2, '李四', '语文', 54);
  20. 20 insert into kecheng (id, name, course, score)
  21. 21 values (2, '李四', '数学', 81);
  22. 22 insert into kecheng (id, name, course, score)
  23. 23 values (2, '李四', '英语', 64);
  24. 24 insert into kecheng (id, name, course, score)
  25. 25 values (2, '李四', '历史', 93);
  26. 26 insert into kecheng (id, name, course, score)
  27. 27 values (2, '李四', '化学', 27);
  28. 28 insert into kecheng (id, name, course, score)
  29. 29 values (3, '王五', '语文', 24);
  30. 30 insert into kecheng (id, name, course, score)
  31. 31 values (3, '王五', '数学', 25);
  32. 32 insert into kecheng (id, name, course, score)
  33. 33 values (3, '王五', '英语', 8);
  34. 34 insert into kecheng (id, name, course, score)
  35. 35 values (3, '王五', '历史', 45);
  36. 36 insert into kecheng (id, name, course, score)
  37. 37 values (3, '王五', '化学', 1);
  38. 38 commit;
复制代码
2、Decode方式
  1. SELECT ID,NAME,
  2. SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以
  3. SUM(DECODE(course,'数学',score,0)) 数学,
  4. SUM(DECODE(course,'英语',score,0)) 英语,
  5. SUM(DECODE(course,'历史',score,0)) 历史,
  6. SUM(DECODE(course,'化学',score,0)) 化学
  7. FROM kecheng
  8. GROUP BY ID ,NAME
复制代码


3、case方式
  1. <font face="宋体" size="2">SELECT ID,NAME,
  2. MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
  3. MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
  4. MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,
  5. MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,
  6. MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学
  7. FROM kecheng
  8. GROUP BY ID ,NAME</font>
复制代码


4、wmsys.wm_concat行列转换函数


  1. <font face="宋体" size="2">SELECT ID,NAME,
  2. wmsys.wm_concat(course || ':'||score) course
  3. FROM kecheng
  4. GROUP BY ID ,NAME;</font>
复制代码


5、使用over(partition by t.u_id)用法
  1. SELECT NAME,
  2. wmsys.wm_concat(course ||score)  OVER (PARTITION BY NAME)
  3. FROM kecheng
复制代码
6、使用plsql
  1. DECLARE
  2.   --存放最终的SQL
  3.   LV_SQL VARCHAR2(3000);
  4.   --存放连接的SQL
  5.   SQL_COMMOND VARCHAR2(3000);
  6.   --定义游标
  7.   CURSOR CUR IS
  8.     SELECT COURSE FROM KECHENG GROUP BY COURSE;
  9. BEGIN
  10.   --定义查询开头
  11.   SQL_COMMOND := 'SELECT NAME ';

  13.   FOR I IN CUR LOOP
  14.     --将结果相连接
  15.     SQL_COMMOND := SQL_COMMOND || ' ,SUM(DECODE(course,''' || I.COURSE ||
  16.                    ''',score,0)) ' || I.COURSE;
  17.     DBMS_OUTPUT.PUT_LINE(SQL_COMMOND);
  18.   END LOOP;
  19.   SQL_COMMOND := SQL_COMMOND || ' from KECHENG group by name';
  20.   LV_SQL      := 'INSERT INTO temp_ss  ' || SQL_COMMOND;
  21.   DBMS_OUTPUT.PUT_LINE(LV_SQL);
  22.   EXECUTE IMMEDIATE LV_SQL;
  23. END;
复制代码







作者: 张兴康    时间: 2020-7-26 18:27
作者: fteair    时间: 2020-7-28 14:10
作者: caixuqad    时间: 2020-7-28 17:44




欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/) Powered by Discuz! X3.2