关于多行转字符串
表结构式这样的:
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(2,'b');
INSERT INTO t_row_str VALUES(3,'c');
INSERT INTO t_row_str VALUES(4,'a');
INSERT INTO t_row_str VALUES(5,'d');
INSERT INTO t_row_str VALUES(6,'e');
INSERT INTO t_row_str VALUES(7,'c');
COMMIT;
SELECT * FROM t_row_str;
多行转字符串是这样的:
适用范围: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;
问题是这个方法转化后为什么每个字符串只有3个字符? 怎么改才能够让所有行转化为一个字符串?