Oracle行列轉(zhuǎn)換總結(jié)

1、行列轉(zhuǎn)換包括以下六種情況：1. 列轉(zhuǎn)行2. 行轉(zhuǎn)列3. 多列轉(zhuǎn)換成字符串4. 多行轉(zhuǎn)換成字符串5. 字符串轉(zhuǎn)換成多列6. 字符串轉(zhuǎn)換成多行 首先聲明，有些例子需要如下10g及以后才有的知識：A. 掌握model子句，B. 正則表達式C. 加強的層次查詢1、列轉(zhuǎn)行CREATE TABLE t_col_row(ID INT,c1 VARCHAR2(10),c2 VARCHAR2(10),c3 VARCHAR2(10); INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');IN

2、SERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);INSERT INTO t_col_row VALUES (6, NU

3、LL, NULL, 'v35');INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);COMMIT; SELECT * FROM t_col_row; 1).  UNION ALL: 適用范圍：8i,9i,10g及以后版本SELECT id, 'c1' cn, c1 cv  FROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cv   FROM t_col_rowUNION ALLSELE

4、CT id, 'c3' cn, c3 cv  FROM t_col_row;若.空行不需要轉(zhuǎn)換，只需加一個where條件，WHERE COLUMN IS NOT NULL 即可。 2). MODEL適用范圍：10g及以后SELECT id, cn, cv FROM t_col_rowMODELRETURN UPDATED ROWSPARTITION BY (ID)DIMENSION BY (0 AS n)MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)RULES UPSERT ALL(

5、60; cn1 = 'c1',  cn2 = 'c2',  cn3 = 'c3',  cv1 = c10,  cv2 = c20,  cv3 = c30  )ORDER BY ID,cn; 3). collection: 適用范圍：8i,9i,10g及以后版本要創(chuàng)建一個對象和一個集合：CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10);CREATE TYPE cv_varr AS VARRAY(8) OF

6、cv_pair;SELECT id, AS cn, t.cv AS cv  FROM t_col_row,       TABLE(cv_varr(cv_pair('c1', t_col_row.c1),                     cv_pair('c2', t_col_row.c2

7、),                     cv_pair('c3', t_col_row.c3) tORDER BY 1, 2;  2、行轉(zhuǎn)列CREATE TABLE t_row_col ASSELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn,

8、c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row; SELECT * FROM t_row_col ORDER BY 1,2; 1). AGGREGATE FUNCTION: 適用范圍：8i,9i,10g及以后版本SELECT id,       MAX(decode(cn, 'c1', cv, NULL) AS c1,     

9、0; MAX(decode(cn, 'c2', cv, NULL) AS c2,       MAX(decode(cn, 'c3', cv, NULL) AS c3  FROM t_row_colGROUP BY idORDER BY 1; MAX聚集函數(shù)也可以用sum、min、avg等其他聚集函數(shù)替代。sign()函數(shù)根據(jù)某個值是0、正數(shù)還是負數(shù)，分別返回0、1、-1用sign和decode來完成比較字段大小來區(qū)某個字段select decode(sign(字段1-字段2),-1

10、,字段3,字段4) from dual; 被指定的轉(zhuǎn)置列只能有一列，但固定的列可以有多列，請看下面的例子：SELECT mgr, deptno, empno, ename FROM scott.emp ORDER BY 1, 2;SELECT mgr,       deptno,       MAX(decode(empno, '7788', ename, NULL) "7788",    

11、   MAX(decode(empno, '7902', ename, NULL) "7902",       MAX(decode(empno, '7844', ename, NULL) "7844",       MAX(decode(empno, '7521', ename, NULL) "7521",   

12、60;   MAX(decode(empno, '7900', ename, NULL) "7900",       MAX(decode(empno, '7499', ename, NULL) "7499",       MAX(decode(empno, '7654', ename, NULL) "7654"  FROM scott.e

13、mpWHERE mgr IN (7566, 7698)   AND deptno IN (20, 30)GROUP BY mgr, deptnoORDER BY 1, 2;這里轉(zhuǎn)置列為empno，固定列為mgr，deptno。 還有一種行轉(zhuǎn)列的方式，就是相同組中的行值變?yōu)閱蝹€列值，但轉(zhuǎn)置的行值不變?yōu)榱忻篒D               CN_1       

14、;      CV_1               CN_2            CV_2                CN_3 

15、60;            CV_31                c1                       v11&

16、#160;                  c2                   v21              

17、0;      c3                  v312                c1           

18、0;            v12                  c2                   v22    &#

19、160;                c3               3                c1   &

20、#160;                    v13                  c2             

21、0;                                 c3                v334

22、60;               c1                                   

23、              c2                   v24                   

24、60; c3                v345                c1                 

25、0;      v15                  c2                            &

26、#160;                  c3               6                c1  

27、;                                               c2    &

28、#160;                                         c3         

29、60;      v357                c1                            &

30、#160;                    c2                             

31、0;                c3這種情況可以用分析函數(shù)實現(xiàn)：SELECT id,       MAX(decode(rn, 1, cn, NULL) cn_1,       MAX(decode(rn, 1, cv, NULL) cv_1,       MAX(decod

32、e(rn, 2, cn, NULL) cn_2,       MAX(decode(rn, 2, cv, NULL) cv_2,       MAX(decode(rn, 3, cn, NULL) cn_3,       MAX(decode(rn, 3, cv, NULL) cv_3  FROM (SELECT id,       &#

33、160;       cn,               cv,               row_number() over(PARTITION BY id ORDER BY cn, cv) rn     

34、     FROM t_row_col)GROUP BY ID; 2). PL/SQL: 適用范圍：8i,9i,10g及以后版本這種對于行值不固定的情況可以使用。下面是我寫的一個包，包中p_rows_column_real用于前述的第一種不限定列的轉(zhuǎn)換；p_rows_column用于前述的第二種不限定列的轉(zhuǎn)換。CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS  TYPE refc IS REF CURSOR;  PROCEDURE p_print_sql(p_

35、txt VARCHAR2);  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)    RETURN VARCHAR2;  PROCEDURE p_rows_column(p_table      IN VARCHAR2,                

36、;          p_keep_cols  IN VARCHAR2,                          p_pivot_cols IN VARCHAR2,      &#

37、160;                   p_where      IN VARCHAR2 DEFAULT NULL,                    

38、0;     p_refc       IN OUT refc);  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,                       

39、;        p_keep_cols IN VARCHAR2,                               p_pivot_col IN VARCHAR2,    

40、60;                          p_pivot_val IN VARCHAR2,                    &#

41、160;          p_where     IN VARCHAR2 DEFAULT NULL,                               p_r

42、efc      IN OUT refc);END;/CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS  PROCEDURE p_print_sql(p_txt VARCHAR2) IS    v_len INT;  BEGIN    v_len := length(p_txt);    FOR i IN 1 . v_len / 250 + 1 LOOP 

43、60;    dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250);    END LOOP;  END;  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)    RETURN VARCHAR2 IS    v_first INT;    v_last  INT;

44、  BEGIN    IF p_seq < 1 THEN      RETURN NULL;    END IF;    IF p_seq = 1 THEN      IF instr(p_str, p_division, 1, p_seq) = 0 THEN        RETURN p_str;  

45、;    ELSE        RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);      END IF;    ELSE      v_first := instr(p_str, p_division, 1, p_seq - 1);      v_l

46、ast  := instr(p_str, p_division, 1, p_seq);      IF (v_last = 0) THEN        IF (v_first > 0) THEN          RETURN substr(p_str, v_first + 1);        EL

47、SE          RETURN NULL;        END IF;      ELSE        RETURN substr(p_str, v_first + 1, v_last - v_first - 1);      END IF; 

48、60;  END IF;  END f_split_str;  PROCEDURE p_rows_column(p_table      IN VARCHAR2,                          p_keep_cols  IN VARCHAR

49、2,                          p_pivot_cols IN VARCHAR2,                    &#

50、160;     p_where      IN VARCHAR2 DEFAULT NULL,                          p_refc       IN OUT refc) I

51、S    v_sql VARCHAR2(4000);    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;    v_keep v_keep_ind_by;      TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;    v_pivot v

52、_pivot_ind_by;      v_keep_cnt   INT;    v_pivot_cnt  INT;    v_max_cols   INT;    v_partition  VARCHAR2(4000);    v_partition1 VARCHAR2(4000);    v_partition2 VARCHAR2(4

53、000);  BEGIN    v_keep_cnt  := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',') + 1;    v_pivot_cnt := length(p_pivot_cols) -                   length(REPLA

54、CE(p_pivot_cols, ',') + 1;    FOR i IN 1 . v_keep_cnt LOOP      v_keep(i) := f_split_str(p_keep_cols, ',', i);    END LOOP;    FOR j IN 1 . v_pivot_cnt LOOP      v_pivot(j) := f_split_str(p

55、_pivot_cols, ',', j);    END LOOP;    v_sql := 'select max(count(*) from ' | p_table | ' group by '    FOR i IN 1 . v_keep.LAST LOOP      v_sql := v_sql | v_keep(i) | ','    END LOOP;

56、    v_sql := rtrim(v_sql, ',');    EXECUTE IMMEDIATE v_sql      INTO v_max_cols;    v_partition := 'select '    FOR x IN 1 . v_keep.COUNT LOOP      v_partition1 := v_partitio

57、n1 | v_keep(x) | ','    END LOOP;    FOR y IN 1 . v_pivot.COUNT LOOP      v_partition2 := v_partition2 | v_pivot(y) | ','    END LOOP;    v_partition1 := rtrim(v_partition1, ',');  

58、  v_partition2 := rtrim(v_partition2, ',');    v_partition  := v_partition | v_partition1 | ',' | v_partition2 |                    ', row_number() over (partit

59、ion by ' | v_partition1 |                    ' order by ' | v_partition2 | ') rn from ' | p_table;    v_partition  := rtrim(v_partition, ',');  

60、60; v_sql        := 'select '    FOR i IN 1 . v_keep.COUNT LOOP      v_sql := v_sql | v_keep(i) | ','    END LOOP;    FOR i IN 1 . v_max_cols LOOP      FO

61、R j IN 1 . v_pivot.COUNT LOOP        v_sql := v_sql | ' max(decode(rn,' | i | ',' | v_pivot(j) |                 ',null)' | v_pivot(j) | '_' | i | ',

62、'      END LOOP;    END LOOP;    IF p_where IS NOT NULL THEN      v_sql := rtrim(v_sql, ',') | ' from (' | v_partition | ' ' |           

63、    p_where | ') group by '    ELSE      v_sql := rtrim(v_sql, ',') | ' from (' | v_partition |               ') group by '    E

64、ND IF;    FOR i IN 1 . v_keep.COUNT LOOP      v_sql := v_sql | v_keep(i) | ','    END LOOP;    v_sql := rtrim(v_sql, ',');    p_print_sql(v_sql);    OPEN p_refc FOR v_sql;  EXCEPT

65、ION    WHEN OTHERS THEN      OPEN p_refc FOR        SELECT 'x' FROM dual WHERE 0 = 1;  END;  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,       

66、;                        p_keep_cols IN VARCHAR2,                      

67、0;        p_pivot_col IN VARCHAR2,                               p_pivot_val IN VARCHAR2,    &#

68、160;                          p_where     IN VARCHAR2 DEFAULT NULL,              

69、0;                p_refc      IN OUT refc) IS    v_sql VARCHAR2(4000);    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;    v_keep

70、v_keep_ind_by;    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;    v_pivot    v_pivot_ind_by;    v_keep_cnt INT;    v_group_by VARCHAR2(2000);  BEGIN    v_keep_cnt := length(p_kee

71、p_cols) - length(REPLACE(p_keep_cols, ',') + 1;    FOR i IN 1 . v_keep_cnt LOOP      v_keep(i) := f_split_str(p_keep_cols, ',', i);    END LOOP;    v_sql := 'select ' | 'cast(' | p_pivot_col | 

72、            ' as varchar2(200) as ' | p_pivot_col | ' from ' | p_table |             ' group by ' | p_pivot_col;    EXECUTE IMMEDIATE v_sql BULK C

73、OLLECT      INTO v_pivot;    FOR i IN 1 . v_keep.COUNT LOOP      v_group_by := v_group_by | v_keep(i) | ','    END LOOP;    v_group_by := rtrim(v_group_by, ',');    v_sql

74、0;     := 'select ' | v_group_by | ','      FOR x IN 1 . v_pivot.COUNT LOOP      v_sql := v_sql | ' max(decode(' | p_pivot_col | ',' | chr(39) |         

75、      v_pivot(x) | chr(39) | ',' | p_pivot_val |               ',null) as "' | v_pivot(x) | '",'    END LOOP;    v_sql := rtrim(v_sql, ',

76、');    IF p_where IS NOT NULL THEN      v_sql := v_sql | ' from ' | p_table | p_where | ' group by ' |               v_group_by;    ELSE   &

77、#160;  v_sql := v_sql | ' from ' | p_table | ' group by ' | v_group_by;    END IF;    p_print_sql(v_sql);    OPEN p_refc FOR v_sql;  EXCEPTION    WHEN OTHERS THEN      OPEN p_refc FOR 

78、       SELECT 'x' FROM dual WHERE 0 = 1;  END;END;/ 3.多列轉(zhuǎn)換成字符串CREATE TABLE t_col_str ASSELECT * FROM t_col_row;這個比較簡單，用|或concat函數(shù)可以實現(xiàn)：SELECT concat('a','b') FROM dual; 1).  | OR concat :  適用范圍：8i,9i,10g及以后版本SELECT *

79、FROM t_col_str;SELECT ID,c1|','|c2|','|c3 AS c123FROM t_col_str; 4.多行轉(zhuǎn)換成字符串CREATE TABLE t_row_str(ID INT,col VARCHAR2(10);INSERT INTO t_row_str VALUES(1,'a');INSERT INTO t_row_str VALUES(1,'b');INSERT INTO t_row_str VALUES(1,'c');INSERT INTO t_row_str VAL

80、UES(2,'a');INSERT INTO t_row_str VALUES(2,'d');INSERT INTO t_row_str VALUES(2,'e');INSERT INTO t_row_str VALUES(3,'c');COMMIT;SELECT * FROM t_row_str; 1) .  MAX + decode : 適用范圍：8i,9i,10g及以后版本SELECT id,       MAX(decode(rn,

81、1, col, NULL) |       MAX(decode(rn, 2, ',' | col, NULL) |       MAX(decode(rn, 3, ',' | col, NULL) str  FROM (SELECT id,               col, 

82、60;             row_number() over(PARTITION BY id ORDER BY col) AS rn          FROM t_row_str) tGROUP BY idORDER BY 1; 2). row_number + lead : 適用范圍：8i,9i,10g及以后版本SELECT id, str  FR

83、OM (SELECT id,               row_number() over(PARTITION BY id ORDER BY col) AS rn,               col | lead(',' | col, 1) over(PARTITION BY id ORDER BY

84、 col) |               lead(',' | col, 2) over(PARTITION BY id ORDER BY col) |               lead(',' | col, 3) over(PARTITION BY id ORDER BY col

85、) AS str          FROM t_row_str)WHERE rn = 1ORDER BY 1; 3). MODEL: 適用范圍：10g及以后版本SELECT id, substr(str, 2) str FROM t_row_strMODELRETURN UPDATED ROWSPARTITION BY(ID)DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)MEASURES (CAS

86、T(col AS VARCHAR2(20) AS str)RULES UPSERTITERATE(3) UNTIL( presentv(striteration_number+2,1,0)=0)           (str0 = str0 | ',' | striteration_number+1)ORDER BY 1;           4) . sys_conne

87、ct_by_path : 適用范圍：8i,9i,10g及以后版本SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2) str  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn          FROM t_row_str) tSTART WITH rn = 1CONNECT BY rn = PRIOR

88、 rn + 1       AND id = PRIOR idGROUP BY t.id; 適用范圍：10g及以后版本SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn          FR

89、OM t_row_str) tWHERE connect_by_isleaf = 1START WITH rn = 1CONNECT BY rn = PRIOR rn + 1       AND id = PRIOR id; 5) . wmsys.wm_concat:  適用范圍：10g及以后版本這個函數(shù)預(yù)定義按','分隔字符串，若要用其他符號分隔可以用，replace將','替換。SELECT id, REPLACE(wmsys.wm_concat(col), '

90、,', '/')  FROM t_row_strGROUP BY id; 5.字符串轉(zhuǎn)換成多列其實際上就是一個字符串拆分的問題。 CREATE TABLE t_str_col ASSELECT ID,c1|','|c2|','|c3 AS c123FROM t_col_str;SELECT * FROM t_str_col; 1). substr + instr : 適用范圍：8i,9i,10g及以后版本SELECT id,       c

91、123,       substr(c123, 1, instr(c123 | ',', ',', 1, 1) - 1) c1,       substr(c123,              instr(c123 | ',', ',', 1, 1) + 1,  &

92、#160;           instr(c123 | ',', ',', 1, 2) - instr(c123 | ',', ',', 1, 1) - 1) c2,       substr(c123,              instr(

93、c123 | ',', ',', 1, 2) + 1,              instr(c123 | ',', ',', 1, 3) - instr(c123 | ',', ',', 1, 2) - 1) c3  FROM t_str_colORDER BY 1; 2) .  regexp_substr : 適用范圍：10g及以后版本S

94、ELECT id,       c123,       rtrim(regexp_substr(c123 | ',', '.*?' | ',', 1, 1), ',') AS c1,       rtrim(regexp_substr(c123 | ',', '.*?' | ',', 1, 2), '

95、;,') AS c2,       rtrim(regexp_substr(c123 | ',', '.*?' | ',', 1, 3), ',') AS c3  FROM t_str_colORDER BY 1; 6.字符串轉(zhuǎn)換成多行CREATE TABLE t_str_row ASSELECT id,       MAX(decode(rn, 1, col, NULL) |

96、0;      MAX(decode(rn, 2, ',' | col, NULL) |       MAX(decode(rn, 3, ',' | col, NULL) str  FROM (SELECT id,               col,    

97、0;          row_number() over(PARTITION BY id ORDER BY col) AS rn          FROM t_row_str) tGROUP BY idORDER BY 1; SELECT * FROM t_str_row; 1) . UNION ALL : 適用范圍：8i,9i,10g及以后版本SELECT id, 1 AS p, subst

98、r(str, 1, instr(str | ',', ',', 1, 1) - 1) AS cv  FROM t_str_rowUNION ALLSELECT id,       2 AS p,       substr(str,              instr(str | ',',

99、',', 1, 1) + 1,              instr(str | ',', ',', 1, 2) - instr(str | ',', ',', 1, 1) - 1) AS cv  FROM t_str_rowUNION ALLSELECT id,       3 AS p,  &#

100、160;    substr(str,              instr(str | ',', ',', 1, 1) + 1,              instr(str | ',', ',', 1, 2) - instr(str | &#

101、39;,', ',', 1, 1) - 1) AS cv  FROM t_str_rowORDER BY 1, 2; 適用范圍：10g及以后版本SELECT id, 1 AS p, rtrim(regexp_substr(str|',', '.*?' | ',', 1, 1), ',') AS cv  FROM t_str_rowUNION ALLSELECT id, 2 AS p, rtrim(regexp_substr(str|',', '.*?&#

102、39; | ',', 1, 2), ',') AS cv  FROM t_str_rowUNION ALLSELECT id, 3 AS p, rtrim(regexp_substr(str|',', '.*?' | ',',1,3), ',') AS cv  FROM t_str_rowORDER BY 1, 2; 2) . VARRAY : 適用范圍：8i,9i,10g及以后版本要創(chuàng)建一個可變數(shù)組：CREATE OR REPLACE TYPE ins_seq_typ

103、e IS VARRAY(8) OF NUMBER;SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5);SELECT t.id,       c.column_value AS p,       substr(t.ca,              instr(t.ca, ',', 1, c.

104、column_value) + 1,              instr(t.ca, ',', 1, c.column_value + 1) -              (instr(t.ca, ',', 1, c.column_value) + 1) AS cv  FROM (SELECT

105、id,               ',' | str | ',' AS ca,               length(str | ',') - nvl(length(REPLACE(str, ','), 0) AS cnt  &#

106、160;       FROM t_str_row) tINNER JOIN TABLE(ins_seq_type(1, 2, 3) c ON c.column_value <=                             

107、0;                       tORDER BY 1, 2; 3). SEQUENCE series : 這類方法主要是要產(chǎn)生一個連續(xù)的整數(shù)列，產(chǎn)生連續(xù)整數(shù)列的方法有很多，主要有：CONNECT BY,ROWNUM+all_objects,CUBE等。適用范圍：8i,9i,10g及以后版本SELECT t.id,  &

108、#160;    c.lv AS p,       substr(t.ca,              instr(t.ca, ',', 1, c.lv) + 1,              instr(t.ca, ',

109、9;, 1, c.lv + 1) -              (instr(t.ca, ',', 1, c.lv) + 1) AS cv  FROM (SELECT id,               ',' | str | ',' AS ca,  

110、             length(str | ',') - nvl(length(REPLACE(str, ','), 0) AS cnt          FROM t_str_row) t,       (SELECT LEVEL lv FROM dual CONNECT BY LEVE

111、L <= 5) cWHERE c.lv <= tORDER BY 1, 2;SELECT t.id,       c.rn AS p,       substr(t.ca,              instr(t.ca, ',', 1, c.rn) + 1,     &

112、#160;        instr(t.ca, ',', 1, c.rn + 1) -              (instr(t.ca, ',', 1, c.rn) + 1) AS cv  FROM (SELECT id,               ',' | str | ',' AS ca,               length(str | ',') - nvl(length(REPLACE(str, ','),