By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE tree (TreeID, ItemID, ParentItemID) AS
SELECT 1, 1, 0 FROM DUAL UNION ALL
SELECT 2, 2, 1 FROM DUAL UNION ALL
SELECT 3, 3, 2 FROM DUAL UNION ALL
SELECT 4, 4, 2 FROM DUAL UNION ALL
SELECT 5, 5, 0 FROM DUAL UNION ALL
SELECT 6, 6, 5 FROM DUAL UNION ALL
SELECT 7, 7, 6 FROM DUAL UNION ALL
SELECT 8, 7, 4 FROM DUAL;
8 rows affected
CREATE TABLE itemnames (ItemID, ItemName) AS
SELECT 0, NULL FROM DUAL UNION ALL
SELECT 1, 'RootFileA' FROM DUAL UNION ALL
SELECT 2, 'FileA' FROM DUAL UNION ALL
SELECT 3, 'ItemA' FROM DUAL UNION ALL
SELECT 4, 'FileB' FROM DUAL UNION ALL
SELECT 5, 'RootFileB' FROM DUAL UNION ALL
SELECT 6, 'FileC' FROM DUAL UNION ALL
SELECT 7, 'ItemB' FROM DUAL
8 rows affected
SELECT *
FROM (
SELECT CONNECT_BY_ROOT treeid AS treeid,
CONNECT_BY_ROOT i.itemid AS itemid,
MAX(LEVEL) OVER(PARTITION BY CONNECT_BY_ROOT treeid) - LEVEL + 1 AS lvl,
i.itemname
FROM tree t
INNER JOIN itemnames i
ON (t.itemId = i.itemId)
CONNECT BY t.itemId = PRIOR t.parentItemId
)
PIVOT (
MAX(itemname)
FOR lvl IN (
4 AS itemname1,
3 AS itemname2,
2 AS itemname3,
1 AS itemname4
)
)
ORDER BY treeid
| TREEID | ITEMID | ITEMNAME1 | ITEMNAME2 | ITEMNAME3 | ITEMNAME4 |
|---|---|---|---|---|---|
| 1 | 1 | null | null | null | RootFileA |
| 2 | 2 | null | null | FileA | RootFileA |
| 3 | 3 | null | ItemA | FileA | RootFileA |
| 4 | 4 | null | FileB | FileA | RootFileA |
| 5 | 5 | null | null | null | RootFileB |
| 6 | 6 | null | null | FileC | RootFileB |
| 7 | 7 | null | ItemB | FileC | RootFileB |
| 8 | 7 | ItemB | FileB | FileA | RootFileA |
SELECT *
FROM (
SELECT CONNECT_BY_ROOT treeid AS treeid,
CONNECT_BY_ROOT i.itemid AS itemid,
MAX(LEVEL) OVER(PARTITION BY CONNECT_BY_ROOT treeid) - LEVEL + 1 AS lvl,
i.itemname
FROM tree t
INNER JOIN itemnames i
ON (t.itemId = i.itemId)
CONNECT BY t.itemId = PRIOR t.parentItemId
)
PIVOT (
MAX(itemname)
FOR lvl IN (
5 AS itemname1,
4 AS itemname2,
3 AS itemname3,
2 AS itemname4,
1 AS itemname5
)
)
ORDER BY treeid
| TREEID | ITEMID | ITEMNAME1 | ITEMNAME2 | ITEMNAME3 | ITEMNAME4 | ITEMNAME5 |
|---|---|---|---|---|---|---|
| 1 | 1 | null | null | null | null | RootFileA |
| 2 | 2 | null | null | null | FileA | RootFileA |
| 3 | 3 | null | null | ItemA | FileA | RootFileA |
| 4 | 4 | null | null | FileB | FileA | RootFileA |
| 5 | 5 | null | null | null | null | RootFileB |
| 6 | 6 | null | null | null | FileC | RootFileB |
| 7 | 7 | null | null | ItemB | FileC | RootFileB |
| 8 | 7 | null | ItemB | FileB | FileA | RootFileA |
SELECT *
FROM (
SELECT CONNECT_BY_ROOT treeid AS treeid,
CONNECT_BY_ROOT i.itemid AS itemid,
MAX(LEVEL) OVER(PARTITION BY CONNECT_BY_ROOT treeid) - LEVEL + 1 AS lvl,
i.itemname
FROM tree t
INNER JOIN itemnames i
ON (t.itemId = i.itemId)
CONNECT BY t.itemId = PRIOR t.parentItemId
)
PIVOT (
MAX(itemname)
FOR lvl IN (
3 AS itemname1,
2 AS itemname2,
1 AS itemname3
)
)
ORDER BY treeid
| TREEID | ITEMID | ITEMNAME1 | ITEMNAME2 | ITEMNAME3 |
|---|---|---|---|---|
| 1 | 1 | null | null | RootFileA |
| 2 | 2 | null | FileA | RootFileA |
| 3 | 3 | ItemA | FileA | RootFileA |
| 4 | 4 | FileB | FileA | RootFileA |
| 5 | 5 | null | null | RootFileB |
| 6 | 6 | null | FileC | RootFileB |
| 7 | 7 | ItemB | FileC | RootFileB |
| 8 | 7 | FileB | FileA | RootFileA |
SELECT CONNECT_BY_ROOT treeid AS treeid,
CONNECT_BY_ROOT i.itemid AS itemid,
MAX(LEVEL) OVER(PARTITION BY CONNECT_BY_ROOT treeid) - LEVEL + 1 AS lvl,
i.itemname
FROM tree t
INNER JOIN itemnames i
ON (t.itemId = i.itemId)
CONNECT BY t.itemId = PRIOR t.parentItemId;
| TREEID | ITEMID | LVL | ITEMNAME |
|---|---|---|---|
| 1 | 1 | 1 | RootFileA |
| 2 | 2 | 2 | FileA |
| 2 | 2 | 1 | RootFileA |
| 3 | 3 | 3 | ItemA |
| 3 | 3 | 2 | FileA |
| 3 | 3 | 1 | RootFileA |
| 4 | 4 | 3 | FileB |
| 4 | 4 | 2 | FileA |
| 4 | 4 | 1 | RootFileA |
| 5 | 5 | 1 | RootFileB |
| 6 | 6 | 2 | FileC |
| 6 | 6 | 1 | RootFileB |
| 7 | 7 | 2 | FileC |
| 7 | 7 | 3 | ItemB |
| 7 | 7 | 1 | RootFileB |
| 8 | 7 | 1 | RootFileA |
| 8 | 7 | 3 | FileB |
| 8 | 7 | 4 | ItemB |
| 8 | 7 | 2 | FileA |