Oracle的行列轉換

行列轉換包括以下六種情況：

1. 列轉行

2. 行轉列

3. 多列轉換成字串

4. 多行轉換成字串

5. 字串轉換成多列

6. 字串轉換成多行

首先聲明，有些例子需要如下10g及以後才有的知識：

A. 掌握model子句，

B. Regex

C. 加強的層次查詢

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 即可。

更多精彩內容：http://www.bianceng.cn/database/Oracle/

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等其他聚集合函式替代。

sign（）函數根據某個值是0、正數還是負數，分別返回0、1、-1

用sign和decode來完成比較欄位大小來區某個欄位

select decode（sign（欄位1-欄位2），-1,欄位3,欄位4） from dual;

被指定的轉置列只能有一列，但固定的列可以有多列，請看下面的例子：

SELECT mgr, deptno, empno, ename FROM scott.emp ORDER BY 1, 2;

SELECT mgr,

deptno,

MAX（decode（empno, '7788', ename, NULL）） "7788",

MAX（decode（empno, '7902', ename, NULL）） "7902",

MAX（decode（empno, '7844', ename, NULL）） "7844",

MAX（decode（empno, '7521', ename, NULL）） "7521",

MAX（decode（empno, '7900', ename, NULL）） "7900",

MAX（decode（empno, '7499', ename, NULL）） "7499",

MAX（decode（empno, '7654', ename, NULL）） "7654"

FROM scott.emp

WHERE mgr IN （7566, 7698）

AND deptno IN （20, 30）

GROUP BY mgr, deptno

ORDER BY 1, 2;

這裡轉置列為empno，固定列為mgr，deptno。

還有一種行轉列的方式，就是相同組中的行值變為單個列值，但轉置的行值不變為列名：

ID               CN_1             CV_1               CN_2            CV_2                CN_3              CV_3

1                c1                       v11                   c2                   v21                     c3                  v31

2                c1                        v12                  c2                   v22                     c3