当前位置: 首页 > >

行列转换总结

发布时间:

行列转换总结
Caizhuoyi
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 INSERT INSERT INSERT INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); INTO t_col_row VALUES (2, 'v12', 'v22', NULL); INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); 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.1 UNION ALL
适用范围:8i,9i,10g 及以后版本 SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row; 若空行不需要转换,只需加一个 where 条件, WHERE COLUMN IS NOT NULL 即可。

2.2 MODEL
适用范围:10g 及以后 SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION 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.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))) t ORDER BY 1, 2;

3. 行转列
CREATE TABLE t_row_col AS SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row; SELECT * FROM t_row_col ORDER BY 1,2;

3.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_col GROUP BY id ORDER BY 1; MAX 聚集函数也可以用 sum、min、avg 等其他聚集函数替代。 被指定 的转置列只能有一列,但固定的列可以有多列,请看下面的例子: SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;




友情链接: