Oracle Hierarchical Query 将不同的级别放入不同的列中

Question

我有这样的源数据

Childid | Parent ID
------- | ---------
1 | NULL
2 | 1
3 | 1
4 | 2
5 | 4
6 | 5
7 | 6

我需要一个 oracle 查询来显示这样的输出。

Child | L1Parent | l2Parent | L3Parent | L4Parent | L5Parent
----- | -------- | -------- | -------- | -------- | --------
1 | NULL
2 | 1
3 | 1
4 | 1 | 2
5 | 1 | 2 | 4
6 | 1 | 2 | 4 | 5
7 | 1 | 2 | 4 | 5 | 6

Answer 1

在我看来这更容易:

SELECT childId,
       trim(SYS_CONNECT_BY_PATH(decode(level,2,parentId,''), ' ')) AS L1,
       trim(SYS_CONNECT_BY_PATH(decode(level,3,parentId,''), ' ')) AS L2,
       trim(SYS_CONNECT_BY_PATH(decode(level,4,parentId,''), ' ')) AS L3,
       trim(SYS_CONNECT_BY_PATH(decode(level,5,parentId,''), ' ')) AS L4,
       trim(SYS_CONNECT_BY_PATH(decode(level,6,parentId,''), ' ')) AS L5,
       trim(SYS_CONNECT_BY_PATH(decode(level,7,parentId,''), ' ')) AS L6,
       trim(SYS_CONNECT_BY_PATH(decode(level,8,parentId,''), ' ')) AS L7
FROM table_name
START WITH parentId is null
CONNECT BY PRIOR childId = parentId;