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 ';
    
12. 
    
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;

复制代码

张兴康

fteair

caixuqad

张兴康

caixuqad