Oracle 行列转换包括以下六种情况:(一)(二)(三)
来源:互联网 发布:工作记录软件windows 编辑:程序博客网 时间:2024/05/09 04:15
行列转换包括以下六种情况:
1. 列转行
2. 行转列
3. 多列转换成字符串
4. 多行转换成字符串
5. 字符串转换成多列
6. 字符串转换成多行
首先声明,有些例子需要如下10g及以后才有的知识:
A. 掌握model子句,
B. 正则表达式
C. 加强的层次查询
1、列转行
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;
1). UNION ALL: 适用范围:8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv FROM t_col_rowUNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_rowUNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row;
若.空行不需要转换,只需加一个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( 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;
3). collection: 适用范围: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 cv FROM 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;
2、行转列
CREATE TABLE t_row_col ASSELECT id, 'c1' cn, c1 cv FROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cv FROM 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, 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聚集函数也可以用sum、min、avg等其他聚集函数替代。
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
用sign和decode来完成比较字段大小来区某个字段
select decode(sign(字段1-字段2),-1,字段3,字段4) from dual;
被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:
SELECT mgr, deptno, empno, ename FROM scott.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 scott.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_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 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_3 FROM (SELECT id, cn, cv, row_number() over(PARTITION BY id ORDER BY cn, cv) rn FROM t_row_col)GROUP BY ID;
2). PL/SQL: 适用范围:8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包,包中
p_rows_column_real用于前述的第一种不限定列的转换;
p_rows_column用于前述的第二种不限定列的转换。
CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS TYPE 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 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 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; 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; 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_last := 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); ELSE RETURN NULL; END IF; ELSE RETURN 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) IS 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_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); BEGIN v_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 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_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; 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_partition1 || 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, ','); 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 LOOP v_sql := v_sql || v_keep(i) || ','; END LOOP; FOR i IN 1 .. v_max_cols LOOP FOR j IN 1 .. v_pivot.COUNT LOOP v_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 THEN v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' || p_where || ') group by '; ELSE v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ') group by '; END 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; EXCEPTION WHEN OTHERS THEN OPEN p_refc FOR SELECT '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) IS 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_pivot_ind_by; v_keep_cnt INT; v_group_by VARCHAR2(2000); BEGIN v_keep_cnt := length(p_keep_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 || ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table || ' group by ' || p_pivot_col; EXECUTE IMMEDIATE v_sql BULK COLLECT 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 := 'select ' || v_group_by || ','; FOR x IN 1 .. v_pivot.COUNT LOOP v_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 THEN v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' || v_group_by; ELSE 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 SELECT 'x' FROM dual WHERE 0 = 1; END;END;/
3.多列转换成字符串
CREATE TABLE t_col_str ASSELECT * FROM t_col_row;
这个比较简单,用||或concat函数可以实现:
SELECT concat('a','b') FROM dual;
1). || OR concat : 适用范围:8i,9i,10g及以后版本
SELECT * FROM t_col_str;SELECT ID,c1||','||c2||','||c3 AS c123FROM t_col_str;
- Oracle 行列转换包括以下六种情况:(一)(二)(三)
- Oracle 行列转换包括以下六种情况:(四)(五)(六)
- 行列转换(二)
- Oracle行列转换的几种方法
- ORACLE行列转换实例
- oracle 行列转换
- 行列转换 oracle
- ORACLE DB行列转换
- oracle行列转换总结
- oracle 行列转换
- 关于oracle行列转换
- oracle 实现行列转换
- oracle行列转换总结
- Oracle 行列转换 总结
- oracle行列转换总结
- oracle行列转换
- oracle 行列转换
- oracle 行列转换
- SQL日期格式化应用大全
- Win2003 远程桌面连接出错解决办法(由于协议错误,会话将被中断。请重新连接到远程计算机)
- SQL Server2005 导入时出现* 错误 0xc00470fe: 数据流任务: 产品级别对于
- Oracle 中的nvl和sql server中的isnull
- JS 常用功能
- Oracle 行列转换包括以下六种情况:(一)(二)(三)
- IOS文件路径处理方法
- 音频格式、编解码
- Oracle 行列转换包括以下六种情况:(四)(五)(六)
- 有关onpropertychange事件
- propertychange/input事件
- windows7安装配置IIS7.5过程图解
- IIS6中附件大小的控制
- Oracle数据库的备份方法