/*
將多筆資料pipe成一個column
使用 Recursive SQL + Self join Table
*/
/* Create testing Table & Data */
CREATE TABLE SAMPLE
(
COL1 INTEGER,
COL2 CHAR(3)
) IN USERSPACE1
INSERT INTO SAMPLE VALUES (1,'111');
INSERT INTO SAMPLE VALUES (1,'222');
INSERT INTO SAMPLE VALUES (1,'333');
INSERT INTO SAMPLE VALUES (1,'444');
INSERT INTO SAMPLE VALUES (2,'AAA');
INSERT INTO SAMPLE VALUES (2,'BBB');
INSERT INTO SAMPLE VALUES (2,'CCC');
COMMIT;
/*
想辦法將資料做成可以 self join 的資料
以下面 SQL 為例，self join 時可以用 PRECURSOR 欄位 join 到 RID 欄位
*/
SELECT COL1,COL2,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM SAMPLE
/* 使用剛才的 SQL 再搭配 Recursive SQL 將 COL2 PIPE 起來，展開資料如下 */
SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT COL1,COL2,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR
FROM SAMPLE
) TMP
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1
/*
對SQL再做些微修改：
1. 增加欄位 CNT 記錄 PIPE 起來的筆數
2. 增加WHERE條件，取 RID = CNT 的資料
*/
SELECT COL1,SUBSTR(SYS_CONNECT_BY_PATH(COL2,','),2) HIST
FROM
(
SELECT COL1,COL2,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) RID,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) - 1 PRECURSOR,
COUNT(*) OVER(PARTITION BY COL1) CNT
FROM SAMPLE
) TMP
WHERE RID = CNT
START WITH RID = 1
CONNECT BY PRIOR RID = PRECURSOR AND PRIOR COL1 = COL1
