oracle一列变6行函数,Oracle行列转换的一点小总结(6)

字符串转换成多行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;

1)UNION ALL

适用范围：8i,9i,10g及以后版本

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;

适用范围：10g及以后版本

SELECT id, 1 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’, 1, 1), ‘,’) AS cv

FROM t_str_row

UNION ALL

SELECT id, 2 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’, 1, 2), ‘,’) AS cv

FROM t_str_row

UNION ALL

SELECT id, 3 AS p, rtrim(regexp_substr(str||’,’, ‘.*?’ || ‘,’,1,3), ‘,’) AS cv

FROM t_str_row

ORDER BY 1, 2;

2)VARRAY

适用范围：8i,9i,10g及以后版本

要创建一个可变数组：

CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));

SELECT t.id,

c.column_value AS p,

substr(t.ca,

instr(t.ca, ‘,’, 1, c.column_value) + 1,

instr(t.ca, ‘,’, 1, c.column_value + 1) –

(instr(t.ca, ‘,’, 1, c.column_value) + 1)) AS cv

FROM (SELECT id,

‘,’ || str || ‘,’ AS ca,

length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt

FROM t_str_row) t

INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=

t.cnt

ORDER BY 1, 2;

3)SEQUENCE series

这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：

CONNECT BY,ROWNUM+all_objects,CUBE等。

适用范围：8i,9i,10g及以后版本

SELECT t.id,

c.lv AS p,

substr(t.ca,

instr(t.ca, ‘,’, 1, c.lv) + 1,

instr(t.ca, ‘,’, 1, c.lv + 1) –

(instr(t.ca, ‘,’, 1, c.lv) + 1)) AS cv

FROM (SELECT id,

‘,’ || str || ‘,’ AS ca,

length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt

FROM t_str_row) t,

(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c

WHERE c.lv <= t.cnt

ORDER BY 1, 2;

SELECT t.id,

c.rn AS p,

substr(t.ca,

instr(t.ca, ‘,’, 1, c.rn) + 1,

instr(t.ca, ‘,’, 1, c.rn + 1) –

(instr(t.ca, ‘,’, 1, c.rn) + 1)) AS cv

FROM (SELECT id,

‘,’ || str || ‘,’ AS ca,

length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt

FROM t_str_row) t,

(SELECT rownum rn FROM all_objects WHERE rownum <= 5) c

WHERE c.rn <= t.cnt

ORDER BY 1, 2;

SELECT t.id,

c.cb AS p,

substr(t.ca,

instr(t.ca, ‘,’, 1, c.cb) + 1,

instr(t.ca, ‘,’, 1, c.cb + 1) –

(instr(t.ca, ‘,’, 1, c.cb) + 1)) AS cv

FROM (SELECT id,

‘,’ || str || ‘,’ AS ca,

length(str || ‘,’) – nvl(length(REPLACE(str, ‘,’)), 0) AS cnt

FROM t_str_row) t,

(SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c

WHERE c.cb <= t.cnt

ORDER BY 1, 2;

适用范围：10g及以后版本

SELECT t.id,

c.lv AS p,

rtrim(regexp_substr(t.str || ‘,’, ‘.*?’ || ‘,’, 1, c.lv), ‘,’) AS cv

FROM (SELECT id,

str,

length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL)) AS cnt

FROM t_str_row) t

INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <=

t.cnt

ORDER BY 1, 2;

4)Hierarchical + DBMS_RANDOM

适用范围：10g及以后版本

SELECT id,

LEVEL AS p,

rtrim(regexp_substr(str || ‘,’, ‘.*?’ || ‘,’, 1, LEVEL), ‘,’) AS cv

FROM t_str_row

CONNECT BY id = PRIOR id

AND PRIOR dbms_random.VALUE IS NOT NULL

AND LEVEL <=

length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL))

ORDER BY 1, 2;

5)Hierarchical + CONNECT_BY_ROOT

适用范围：10g及以后版本

SELECT id,

LEVEL AS p,

rtrim(regexp_substr(str || ‘,’, ‘.*?’ || ‘,’, 1, LEVEL), ‘,’) AS cv

FROM t_str_row

CONNECT BY id = connect_by_root id

AND LEVEL <=

length(regexp_replace(str || ‘,’, ‘[^’ || ‘,’ || ‘]’, NULL))

ORDER BY 1, 2;

6)MODEL

适用范围：10g及以后版本

SELECT id, p, cv FROM t_str_row

MODEL

RETURN UPDATED ROWS

PARTITION BY(ID)

DIMENSION BY( 0 AS p)

MEASURES( str||’,’ AS cv)

RULES UPSERT

(cv

[ FOR p

FROM 1 TO length(regexp_replace(cv[0],'[^’||’,’||’]’,null))

INCREMENT 1

] = rtrim(regexp_substr( cv[0],’.*?’||’,’,1,cv(p)),’,’))

ORDER BY 1,2;

end.

转载声明：本站文章若无特别说明，皆为原创，转载请注明来源：三十岁，谢谢！^^

分享到：