SQL 行列转换的的总结.pdfVIP

行列转换总结 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 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.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 FR