数据库列转行、行转列.docVIP

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_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)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; 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; 2、行转列 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; 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; SELECT mgr, deptno, MAX(decode(empno, 7788, ename, NULL)) 7788,