oracle行转列问题,Oracle行列转换问题

--列转行

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;

SELECT id, 'c1' cn, c1 cv

FROM t_col_row WHERE c1 IS NOT NULL

UNION ALL

SELECT id, 'c2' cn, c2 cv

FROM t_col_row WHERE c2 IS NOT NULL

UNION ALL

SELECT id, 'c3' cn, c3 cv FROM t_col_row

WHERE c3 IS NOT NULL

--行转列

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 id, Cv c1 from t_row_col

select * from t_row_col

select * from t_col_row

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

--多列转换成字符串

CREATE TABLE t_col_str AS

SELECT * FROM t_col_row;

SELECT ID, c1 || c2 || c3 AS c123 FROM t_col_str;

--多行转换成字符串

CREATE TABLE t_row_str(

ID INT,

col VARCHAR2(10));

INSERT INTO t_row_str VALUES(1,'a');

INSERT INTO t_row_str VALUES(1,'b');

INSERT INTO t_row_str VALUES(1,'c');

INSERT INTO t_row_str VALUES(2,'a');

INSERT INTO t_row_str VALUES(2,'d');

INSERT INTO t_row_str VALUES(2,'e');

INSERT INTO t_row_str VALUES(3,'c');

COMMIT;

select * from t_row_str

SELECT id,

MAX(decode(rn, 1, col, NULL)) ||

MAX(decode(rn, 2, ',' || col, NULL)) ||

MAX(decode(rn, 3, ',' || col, NULL)) str

FROM (SELECT id,

col,

row_number() over(PARTITION BY id ORDER BY col) AS rn

FROM t_row_str) t

GROUP BY id

ORDER BY 1;

--字符串转换成多列

CREATE TABLE t_str_col AS

SELECT ID,c1||','||c2||','||c3 AS c123

FROM t_col_str;

SELECT * FROM t_str_col;

SELECT id,

c123,

substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,

substr(c123,

instr(c123 || ',', ',', 1, 1) + 1,

instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,

substr(c123,

instr(c123 || ',', ',', 1, 2) + 1,

instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3

FROM t_str_col

ORDER BY 1;

--字符串转换成多行

CREATE TABLE t_str_row AS

SELECT id,

MAX(decode(rn, 1, col, NULL)) ||

MAX(decode(rn, 2, ',' || col, NULL)) ||

MAX(decode(rn, 3, ',' || col, NULL)) str

FROM (SELECT id,

col,

row_number() over(PARTITION BY id ORDER BY col) AS rn

FROM t_row_str) t

GROUP BY id

ORDER BY 1;

SELECT * FROM t_str_row;

SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv

FROM t_str_row

UNION ALL

SELECT id,

2 AS p,

substr(str,

instr(str || ',', ',', 1, 1) + 1,

instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv

FROM t_str_row

UNION ALL

SELECT id,

3 AS p,

substr(str,

instr(str || ',', ',', 1, 1) + 1,

instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv

FROM t_str_row

ORDER BY 1, 2;[@more@]