Oracle行列轉換，oracle行列

Oracle行列轉換，oracle行列

一、建表與插入數據

1.1、建表

 1 create table kecheng
 2 (
 3   id     NUMBER,
 4   name   VARCHAR2(20),
 5   course VARCHAR2(20),
 6   score  NUMBER
 7 );
 8 insert into kecheng (id, name, course, score)
 9 values (1, '張三', '語文', 67);
10 insert into kecheng (id, name, course, score)
11 values (1, '張三', '數學', 76);
12 insert into kecheng (id, name, course, score)
13 values (1, '張三', '英語', 43);
14 insert into kecheng (id, name, course, score)
15 values (1, '張三', '歷史', 56);
16 insert into kecheng (id, name, course, score)
17 values (1, '張三', '化學', 11);
18 insert into kecheng (id, name, course, score)
19 values (2, '李四', '語文', 54);
20 insert into kecheng (id, name, course, score)
21 values (2, '李四', '數學', 81);
22 insert into kecheng (id, name, course, score)
23 values (2, '李四', '英語', 64);
24 insert into kecheng (id, name, course, score)
25 values (2, '李四', '歷史', 93);
26 insert into kecheng (id, name, course, score)
27 values (2, '李四', '化學', 27);
28 insert into kecheng (id, name, course, score)
29 values (3, '王五', '語文', 24);
30 insert into kecheng (id, name, course, score)
31 values (3, '王五', '數學', 25);
32 insert into kecheng (id, name, course, score)
33 values (3, '王五', '英語', 8);
34 insert into kecheng (id, name, course, score)
35 values (3, '王五', '歷史', 45);
36 insert into kecheng (id, name, course, score)
37 values (3, '王五', '化學', 1);
38 commit;

二、固定行列轉換

2.1、Decode方式

SELECT ID,NAME,
SUM(DECODE(course,'語文',score,0)) 語文,--這裡使用max,min都可以
SUM(DECODE(course,'數學',score,0)) 數學,
SUM(DECODE(course,'英語',score,0)) 英語,
SUM(DECODE(course,'歷史',score,0)) 歷史,
SUM(DECODE(course,'化學',score,0)) 化學
FROM kecheng
GROUP BY ID ,NAME

2.2、Case方式

SELECT ID,NAME,
MAX(CASE WHEN course='語文' THEN score ELSE 0 END) 語文,
MAX(CASE WHEN course='數學' THEN score ELSE 0 END) 數學,
MAX(CASE WHEN course='英語' THEN score ELSE 0 END) 英語,
MAX(CASE WHEN course='歷史' THEN score ELSE 0 END) 歷史,
MAX(CASE WHEN course='化學' THEN score ELSE 0 END) 化學
FROM kecheng
GROUP BY ID ,NAME

結果與上方一樣

2.3、wmsys.wm_concat行列轉換函數

SELECT ID,NAME,
wmsys.wm_concat(course || ':'||score) course
FROM kecheng
GROUP BY ID ,NAME;

2.4、使用over(partition by t.u_id)用法

SELECT NAME,
wmsys.wm_concat(course ||score)  OVER (PARTITION BY NAME)
FROM kecheng

三、動態轉換

3.1、使用PL/SQL

DECLARE
  --存放最終的SQL
  LV_SQL VARCHAR2(3000);
  --存放連接的SQL
  SQL_COMMOND VARCHAR2(3000);
  --定義游標
  CURSOR CUR IS
    SELECT COURSE FROM KECHENG GROUP BY COURSE;
BEGIN
  --定義查詢開頭
  SQL_COMMOND := 'SELECT NAME ';

  FOR I IN CUR LOOP
    --將結果相連接
    SQL_COMMOND := SQL_COMMOND || ' ,SUM(DECODE(course,''' || I.COURSE ||
                   ''',score,0)) ' || I.COURSE;
    DBMS_OUTPUT.PUT_LINE(SQL_COMMOND);
  END LOOP;
  SQL_COMMOND := SQL_COMMOND || ' from KECHENG group by name';
  LV_SQL      := 'INSERT INTO temp_ss  ' || SQL_COMMOND;
  DBMS_OUTPUT.PUT_LINE(LV_SQL);
  EXECUTE IMMEDIATE LV_SQL;
END;

temp_ss  表