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(1,'d');

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

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

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

COMMIT;

SELECT * FROM t_row_str;

---以下的方法需要手工的構造連線字串，因此適合列型別不多的情況。

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;

-----------------------

-----使用內建sys_connect_by_path函式--------可以一次處理

SELECT t.id id,/*rn, */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;

--------------------------------------

SELECT * FROM t_row_str order by id,col

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;

--SELECT t.id id,rn, MAX(substr(sys_connect_by_path(t.col, ','), 2) )str

SELECT t.id id,rn, sys_connect_by_path(t.col, ',') str