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 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;

 

2、行转列

 

1). AGGREGATE FUNCTION: 适 用范围：8i,9i,10g及以后版本

 

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;

 

 

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;