oracle 行转列 列转行

oracle 行转列 列转行

1.概述最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧！行列转换包括以下六种情况：1)列转行2)行转列3)多列转换成字符串4)多行转换成字符串5)字符串转换成多列6)字符串转换成多行下面分别进行举例介绍。首先声明一点，有些例子需要如下10g及以后才有的知识：A.掌握model子句B.正则表达式C.加强的层次查询讨论的适用范围只包括8i,9i,10g及以后版本。2.列转行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');INSERT 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, NULL, NULL, 'v35');INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);COMMIT;SELECT * FROM t_col_row;2.1UNION ALL适用范围：8i,9i,10g及以后版本SELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换，只需加一个where条件，WHERE COLUMN IS NOT NULL 即可。2.2MODEL适用范围：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(cn[1] = 'c1',cn[2] = 'c2',cn[3] = 'c3',cv[1] = c1[0],cv[2] = c2[0],cv[3] = c3[0])ORDER BY ID,cn;2.3COLLECTION适用范围：8i,9i,10g及以后版本要创建一个对象和一个集合：CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;SELECT id, t.cn AS cn, t.cv AS cvFROM t_col_row,TABLE(cv_varr(cv_pair('c1', t_col_row.c1),cv_pair('c2', t_col_row.c2),cv_pair('c3', t_col_row.c3))) tORDER BY 1, 2;3.行转列CREATE TABLE t_row_col ASSELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;SELECT * FROM t_row_col ORDER BY 1,2;3.1AGGREGATE FUNCTION适用范围：8i,9i,10g及以后版本SELECT id,MAX(decode(cn, 'c1', cv, NULL)) AS c1,MAX(decode(cn, 'c2', cv, NULL)) AS c2,MAX(decode(cn, 'c3', cv, NULL)) AS c3FROM t_row_colGROUP BY idORDER BY 1;MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;SELECT mgr,deptno,MAX(decode(empno, '7788', ename, NULL)) "7788",MAX(decode(empno, '7902', ename, NULL)) "7902",MAX(decode(empno, '7844', ename, NULL)) "7844",MAX(decode(empno, '7521', ename, NULL)) "7521",MAX(decode(empno, '7900', ename, NULL)) "7900",MAX(decode(empno, '7499', ename, NULL)) "7499",MAX(decode(empno, '7654', ename, NULL)) "7654"FROM empWHERE mgr IN (7566, 7698)AND deptno IN (20, 30)GROUP BY mgr, deptnoORDER BY 1, 2;这里转置列为empno，固定列为mgr，deptno。还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_31 c1 v11 c2 v21 c3 v312 c1 v12 c2 v22 c33 c1 v13 c2 c3 v334 c1 c2 v24 c3 v345 c1 v15 c2 c36 c1 c2 c3 v357 c1 c2 c3这种情况可以用分析函数实现：SELECT id,MAX(decode(rn, 1, cn, NULL)) cn_1,MAX(decode(rn, 1, cv, NULL)) cv_1,MAX(decode(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_3FROM (SELECT id,cn,cv,row_number() over(PARTITION BY id ORDER BY cn, cv) rnFROM t_row_col)GROUP BY ID;3.2PL/SQL适用范围：8i,9i,10g及以后版本这种对于行值不固定的情况可以使用。下面是我写的一个包，包中p_rows_column_real用于前述的第一种不限定列的转换；p_rows_column用于前述的第二种不限定列的转换。CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column ASTYPE refc IS REF CURSOR;PROCEDURE p_print_sql(p_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,p_keep_cols IN VARCHAR2,p_pivot_cols IN VARCHAR2,p_where IN VARCHAR2 DEFAULT NULL,p_refc IN OUT refc);PROCEDURE p_rows_column_real(p_table IN VARCHAR2,p_keep_cols IN VARCHAR2,p_pivot_col IN VARCHAR2,p_pivot_val IN VARCHAR2,p_where IN VARCHAR2 DEFAULT NULL,p_refc IN OUT refc);END;/CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column ASPROCEDURE p_print_sql(p_txt VARCHAR2) ISv_len INT;BEGINv_len := length(p_txt);FOR i IN 1 .. v_len / 250 + 1 LOOPdbms_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 ISv_first INT;v_last INT;BEGINIF p_seq < 1 THENRETURN NULL;END IF;IF p_seq = 1 THENIF instr(p_str, p_division, 1, p_seq) = 0 THENRETURN p_str;ELSERETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);END IF;ELSEv_first := instr(p_str, p_division, 1, p_seq - 1);v_last := instr(p_str, p_division, 1, p_seq);IF (v_last = 0) THENIF (v_first > 0) THENRETURN substr(p_str, v_first + 1);ELSERETURN NULL;END IF;ELSERETURN substr(p_str, v_first + 1, v_last - v_first - 1);END IF;END IF;END f_split_str;PROCEDURE p_rows_column(p_table IN VARCHAR2,p_keep_cols IN VARCHAR2,p_pivot_cols IN VARCHAR2,p_where IN VARCHAR2 DEFAULT NULL,p_refc IN OUT refc) ISv_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_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(4000);BEGINv_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;v_pivot_cnt := length(p_pivot_cols) -length(REPLACE(p_pivot_cols, ',')) + 1;FOR i IN 1 .. v_keep_cnt LOOPv_keep(i) := f_split_str(p_keep_cols, ',', i);END LOOP;FOR j IN 1 .. v_pivot_cnt LOOPv_pivot(j) := f_split_str(p_pivot_cols, ',', j);END LOOP;v_sql := 'select max(count(*)) from ' || p_table || ' group by ';FOR i IN 1 .. v_keep.LAST LOOPv_sql := v_sql || v_keep(i) || ',';END LOOP;v_sql := rtrim(v_sql, ',');EXECUTE IMMEDIATE v_sqlINTO v_max_cols;v_partition := 'select ';FOR x IN 1 .. v_keep.COUNT LOOPv_partition1 := v_partition1 || v_keep(x) || ',';END LOOP;FOR y IN 1 .. v_pivot.COUNT LOOPv_partition2 := v_partition2 || v_pivot(y) || ',';END LOOP;v_partition1 := rtrim(v_partition1, ',');v_partition2 := rtrim(v_partition2, ',');v_partition := v_partition || v_partition1 || ',' || v_partition2 ||', row_number() over (partition by ' || v_partition1 ||' order by ' || v_partition2 || ') rn from ' || p_table;v_partition := rtrim(v_partition, ',');v_sql := 'select ';FOR i IN 1 .. v_keep.COUNT LOOPv_sql := v_sql || v_keep(i) || ',';END LOOP;FOR i IN 1 .. v_max_cols LOOPFOR j IN 1 .. v_pivot.COUNT LOOPv_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||',null))' || v_pivot(j) || '_' || i || ',';END LOOP;END LOOP;IF p_where IS NOT NULL THENv_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||p_where || ') group by ';ELSEv_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||') group by ';END IF;FOR i IN 1 .. v_keep.COUNT LOOPv_sql := v_sql || v_keep(i) || ',';END LOOP;v_sql := rtrim(v_sql, ',');p_print_sql(v_sql);OPEN p_refc FOR v_sql;EXCEPTIONWHEN OTHERS THENOPEN p_refc FORSELECT 'x' FROM dual WHERE 0 = 1;END;PROCEDURE p_rows_column_real(p_table IN VARCHAR2,p_keep_cols IN VARCHAR2,p_pivot_col IN VARCHAR2,p_pivot_val IN VARCHAR2,p_where IN VARCHAR2 DEFAULT NULL,p_refc IN OUT refc) ISv_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_pivot_ind_by;v_keep_cnt INT;v_group_by VARCHAR2(2000);BEGINv_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;FOR i IN 1 .. v_keep_cnt LOOPv_keep(i) := f_split_str(p_keep_cols, ',', i);END LOOP;v_sql := 'select ' || 'cast(' || p_pivot_col ||' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||' group by ' || p_pivot_col;EXECUTE IMMEDIATE v_sql BULK COLLECTINTO v_pivot;FOR i IN 1 .. v_keep.COUNT LOOPv_group_by := v_group_by || v_keep(i) || ',';END LOOP;v_group_by := rtrim(v_group_by, ',');v_sql := 'select ' || v_group_by || ',';FOR x IN 1 .. v_pivot.COUNT LOOPv_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||v_pivot(x) || chr(39) || ',' || p_pivot_val ||',null)) as "' || v_pivot(x) || '",';END LOOP;v_sql := rtrim(v_sql, ',');IF p_where IS NOT NULL THENv_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||v_group_by;ELSEv_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;END IF;p_print_sql(v_sql);OPEN p_refc FOR v_sql;EXCEPTIONWHEN OTHERS THENOPEN p_refc FORSELECT 'x' FROM dual WHERE 0 = 1;END;END;/4.多列转换成字符串CREATE TABLE t_col_str ASSELECT * FROM t_col_row;这个比较简单，用||或concat函数可以实现：SELECT concat('a','b') FROM dual;4.1|| OR CONCAT适用范围：8i,9i,10g及以后版本SELECT * FROM t_col_str;SELECT ID,c1||','||c2||','||c3 AS c123FROM t_col_str;5.多行转换成字符串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 VALUES(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;5.1MAX + DECODE适用范围：8i,9i,10g及以后版本SELECT id,MAX(decode(rn, 1, col, NULL)) ||MAX(decode(rn, 2, ',' || col, NULL)) ||MAX(decode(rn, 3, ',' || col, NULL)) strFROM (SELECT id,col,row_number() over(PARTITION BY id ORDER BY col) AS rnFROM t_row_str) tGROUP BY idORDER BY 1;5.2ROW_NUMBER + LEAD适用范围：8i,9i,10g及以后版本SELECT id, strFROM (SELECT id,row_number() over(PARTITION BY id ORDER BY col) AS rn,col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS strFROM t_row_str)WHERE rn = 1ORDER BY 1;5.3MODEL适用范围：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 (CAST(col AS VARCHAR2(20)) AS str)RULES UPSERTITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)(str[0] = str[0] || ',' || str[iteration_number+1])ORDER BY 1;5.4SYS_CONNECT_BY_PATH适用范围：8i,9i,10g及以后版本SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) strFROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rnFROM t_row_str) tSTART WITH rn = 1CONNECT BY rn = PRIOR rn + 1AND id = PRIOR idGROUP BY t.id;适用范围：10g及以后版本SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) strFROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rnFROM t_row_str) tWHERE connect_by_isleaf = 1START WITH rn = 1CONNECT BY rn = PRIOR rn + 1AND id = PRIOR id;5.5WMSYS.WM_CONCAT适用范围：10g及以后版本这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') strFROM t_row_strGROUP BY id;6.字符串转换成多列其实际上就是一个字符串拆分的问题。CREATE TABLE t_str_col ASSELECT ID,c1||','||c2||','||c3 AS c123FROM t_col_str;SELECT * FROM t_str_col;6.1SUBSTR + INSTR适用范围：8i,9i,10g及以后版本SELECT id,c123,substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,substr(c123,instr(c123 || ',', ',', 1, 1) + 1,instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,substr(c123,instr(c123 || ',', ',', 1, 2) + 1,instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3FROM t_str_colORDER BY 1;6.2REGEXP_SUBSTR适用范围：10g及以后版本SELECT id,c123,rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 1), ',') AS c1,rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 2), ',') AS c2,rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 3), ',') AS c3FROM t_str_colORDER BY 1;7.字符串转换成多行CREATE TABLE t_str_row ASSELECT id,MAX(decode(rn, 1, col, NULL)) ||MAX(decode(rn, 2, ',' || col, NULL)) ||MAX(decode(rn, 3, ',' || col, NULL)) strFROM (SELECT id,col,row_number() over(PARTITION BY id ORDER BY col) AS rnFROM t_row_str) tGROUP BY idORDER BY 1;SELECT * FROM t_str_row;7.1UNION ALL适用范围：8i,9i,10g及以后版本SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cvFROM t_str_rowUNION ALLSELECT id,2 AS p,substr(str,instr(str || ',', ',', 1, 1) + 1,instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cvFROM t_str_rowUNION ALLSELECT id,3 AS p,substr(str,instr(str || ',', ',', 1, 1) + 1,instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cvFROM t_str_rowORDER BY 1, 2;适用范围：10g及以后版本SELECT id, 1 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 1), ',') AS cvFROM t_str_rowUNION ALLSELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cvFROM t_str_rowUNION ALLSELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cvFROM t_str_rowORDER BY 1, 2;7.2VARRAY适用范围：8i,9i,10g及以后版本要创建一个可变数组：CREATE OR REPLACE TYPE ins_seq_type 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.column_value) + 1,instr(t.ca, ',', 1, c.column_value + 1) -(instr(t.ca, ',', 1, c.column_value) + 1)) AS cvFROM (SELECT id,',' || str || ',' AS ca,length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cntFROM t_str_row) tINNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=t.cntORDER BY 1, 2;7.3SEQUENCE SERIES这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：CONNECT BY,ROWNUM+all_objects,CUBE等。适用范围：8i,9i,10g及以后版本SELECT t.id,c.lv AS p,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) -(instr(t.ca, ',', 1, c.lv) + 1)) AS cvFROM (SELECT id,',' || str || ',' AS ca,length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cntFROM t_str_row) t,(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) cWHERE c.lv <= t.cntORDER BY 1, 2;SELECT t.id,c.rn AS p,substr(t.ca,instr(t.ca, ',', 1, c.rn) + 1,instr(t.ca, ',', 1, c.rn + 1) -(instr(t.ca, ',', 1, c.rn) + 1)) AS cvFROM (SELECT id,',' || str || ',' AS ca,length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cntFROM t_str_row) t,(SELECT rownum rn FROM all_objects WHERE rownum <= 5) cWHERE c.rn <= t.cntORDER BY 1, 2;SELECT t.id,c.cb AS p,substr(t.ca,instr(t.ca, ',', 1, c.cb) + 1,instr(t.ca, ',', 1, c.cb + 1) -(instr(t.ca, ',', 1, c.cb) + 1)) AS cvFROM (SELECT id,',' || str || ',' AS ca,length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cntFROM t_str_row) t,(SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) cWHERE c.cb <= t.cntORDER BY 1, 2;适用范围：10g及以后版本SELECT t.id,c.lv AS p,rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cvFROM (SELECT id,str,length(regexp_replace(str || ',', '[^' || ',' || ']', NULL)) AS cntFROM t_str_row) tINNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <= t.cntORDER BY 1, 2;7.4HIERARCHICAL + DBMS_RANDOM适用范围：10g及以后版本SELECT id,LEVEL AS p,rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cvFROM t_str_rowCONNECT BY id = PRIOR idAND PRIOR dbms_random.VALUE IS NOT NULLAND LEVEL <=length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))ORDER BY 1, 2;7.5HIERARCHICAL + CONNECT_BY_ROOT适用范围：10g及以后版本SELECT id,LEVEL AS p,rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cvFROM t_str_rowCONNECT BY id = connect_by_root idAND LEVEL <=length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))ORDER BY 1, 2;7.6MODEL适用范围：10g及以后版本SELECT id, p, cv FROM t_str_rowMODELRETURN UPDATED ROWSPARTITION BY(ID)DIMENSION BY( 0 AS p)MEASURES( str||',' AS cv)RULES UPSERT(cv[ FOR pFROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))