oracle / parallel(t_4)/,Oracle行列转换的一点小总结(4)

多行转换成字符串

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;

1)MAX + decode

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

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;

2)row_number + lead

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

SELECT id, str

FROM (SELECT id,

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

col || lead(‘,’ || col, 1) over(PARTITION BY id ORDER BY col) ||

lead(‘,’ || col, 2) over(PARTITION BY id ORDER BY col) ||

lead(‘,’ || col, 3) over(PARTITION BY id ORDER BY col) AS str

FROM t_row_str)

WHERE rn = 1

ORDER BY 1;

3)MODEL

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

SELECT id, substr(str, 2) str FROM t_row_str

MODEL

RETURN UPDATED ROWS

PARTITION BY(ID)

DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)

MEASURES (CAST(col AS VARCHAR2(20)) AS str)

RULES UPSERT

ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)

(str[0] = str[0] || ‘,’ || str[iteration_number+1])

ORDER BY 1;

4)sys_connect_by_path

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

SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ‘,’), 2)) str

FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn

FROM t_row_str) t

START WITH rn = 1

CONNECT BY rn = PRIOR rn + 1

AND id = PRIOR id

GROUP BY t.id;

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

SELECT t.id id, substr(sys_connect_by_path(t.col, ‘,’), 2) str

FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn

FROM t_row_str) t

WHERE connect_by_isleaf = 1

START WITH rn = 1

CONNECT BY rn = PRIOR rn + 1

AND id = PRIOR id;

5)wmsys.wm_concat

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

这个函数预定义按’,’分隔字符串，若要用其他符号分隔可以用，replace将’,’替换。

SELECT id, REPLACE(wmsys.wm_concat(col), ‘,’, ‘/’)

FROM t_row_str

GROUP BY id;

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

分享到：