oracle行列转换.doc

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、