SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE table_name ( Col_Old_ID, Col_New_ID ) AS
SELECT 'ID_1', 'ID_2' FROM DUAL UNION ALL
SELECT 'ID_2', 'ID_3' FROM DUAL UNION ALL
SELECT 'ID_A', 'ID_B' FROM DUAL UNION ALL
SELECT 'ID_B', 'ID_C' FROM DUAL UNION ALL
SELECT 'ID_3', 'ID_4' FROM DUAL UNION ALL
SELECT 'ID_4', 'ID_5' FROM DUAL UNION ALL
SELECT 'ID_C', 'ID_D' FROM DUAL;
Query 1:
You can use a hierarchical query to get the history from the current row:
SELECT CONNECT_BY_ROOT( Col_Old_ID ) AS Id,
CONNECT_BY_ROOT( Col_Old_ID )
|| SYS_CONNECT_BY_PATH( Col_New_ID, ',' ) AS History
FROM table_name
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR Col_New_ID = Col_Old_ID
Results:
| ID | HISTORY |
|------|--------------------------|
| ID_1 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_2 | ID_2,ID_3,ID_4,ID_5 |
| ID_3 | ID_3,ID_4,ID_5 |
| ID_4 | ID_4,ID_5 |
| ID_A | ID_A,ID_B,ID_C,ID_D |
| ID_B | ID_B,ID_C,ID_D |
| ID_C | ID_C,ID_D |
Query 2:
If you want the full history then it gets complicated as you need the hierarchy before and after the item.
SELECT id,
HISTORY
FROM (
SELECT Col_Old_ID,
Col_new_id,
MAX(
SUBSTR( SYS_CONNECT_BY_PATH( Col_Old_ID, ',' ), 2 )
|| ',' || Col_New_ID
) OVER (
PARTITION BY CONNECT_BY_ROOT( Col_Old_ID )
ORDER BY CONNECT_BY_ISLEAF DESC
) As history,
CONNECT_BY_ISLEAF AS leaf
FROM table_name
START WITH Col_Old_ID NOT IN ( SELECT Col_New_Id FROM table_name )
CONNECT BY PRIOR Col_New_ID = Col_Old_ID
)
UNPIVOT ( id FOR col_type IN ( Col_Old_Id, Col_New_Id ) )
WHERE col_type = 'COL_OLD_ID'
OR leaf = 1
ORDER BY id
Results:
| ID | HISTORY |
|------|--------------------------|
| ID_1 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_2 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_3 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_4 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_5 | ID_1,ID_2,ID_3,ID_4,ID_5 |
| ID_A | ID_A,ID_B,ID_C,ID_D |
| ID_B | ID_A,ID_B,ID_C,ID_D |
| ID_C | ID_A,ID_B,ID_C,ID_D |
| ID_D | ID_A,ID_B,ID_C,ID_D |
