Oracle行列转换

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

适用范围：8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
   FROM t_col_row
UNION

 SELECT id, 'c2' cn, c2 cv
   FROM t_col_row
UNION 

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;