分层查询，在多行中给出输出，希望在一行中输出

Question

我有一个分层表，我获取分层数据，但它的行如下所示： 表结构 ：

id      parent_type parent_id    
71123   FSE         67114       
67114   DST         67111          
67111   null            0    

层次结构的输出：

id      parent_type parent_id   level   SYS_CONNECT_BY_PATH(parent_type, '->') 
71123   FSE         67114       1      ->FSE
67114   DST         67111       2      ->FSE->DST
67111   null            0       3      ->FSE->DST->
I WANT RESULT OF ABOVE OUTPUT AS SHOWN BELOW:
id   parent_type parent_id   id   parent_type parent_id  id    parent_type parent_id   
71123   FSE      67114       67114 DST        67111      67111 NULL  0

Answer 1

可能看起来太复杂了，但实际上很容易。

WITH dat
AS (SELECT 71123 AS id,
'FSE' AS parent_type,
67114 AS parent_id
FROM   DUAL
UNION
SELECT 67114 AS id,
'DST' AS parent_type,
67111 AS parent_id
FROM   DUAL
UNION
SELECT 67111 AS id,
NULL AS parent_type,
0 AS parent_id
FROM   DUAL)
SELECT MAX (id_level_1) AS id_level_1,
MAX (parent_type_level_1) AS parent_type_level_1,
MAX (parent_id_level_1) AS parent_id_level_1,
MAX (id_level_2) AS id_level_2,
MAX (parent_type_level_2) AS parent_type_level_2,
MAX (parent_id_level_2) AS parent_id_level_2,
MAX (id_level_3) AS id_level_3,
MAX (parent_type_level_3) AS parent_type_level_3,
MAX (parent_id_level_3) AS parent_id_level_3,
MAX (id_level_4) AS id_level_4,
MAX (parent_type_level_4) AS parent_type_level_4,
MAX (parent_id_level_4) AS parent_id_level_4,
MAX (id_level_5) AS id_level_5,
MAX (parent_type_level_5) AS parent_type_level_5,
MAX (parent_id_level_5) AS parent_id_level_5
FROM   (SELECT CASE WHEN l = 1 THEN id ELSE NULL END AS id_level_1,
CASE WHEN l = 1 THEN parent_type ELSE NULL END AS parent_type_level_1,
CASE WHEN l = 1 THEN parent_id ELSE NULL END AS parent_id_level_1,
CASE WHEN l = 2 THEN id ELSE NULL END AS id_level_2,
CASE WHEN l = 2 THEN parent_type ELSE NULL END AS parent_type_level_2,
CASE WHEN l = 2 THEN parent_id ELSE NULL END AS parent_id_level_2,
CASE WHEN l = 3 THEN id ELSE NULL END AS id_level_3,
CASE WHEN l = 3 THEN parent_type ELSE NULL END AS parent_type_level_3,
CASE WHEN l = 3 THEN parent_id ELSE NULL END AS parent_id_level_3,
CASE WHEN l = 4 THEN id ELSE NULL END AS id_level_4,
CASE WHEN l = 4 THEN parent_type ELSE NULL END AS parent_type_level_4,
CASE WHEN l = 4 THEN parent_id ELSE NULL END AS parent_id_level_4,
CASE WHEN l = 5 THEN id ELSE NULL END AS id_level_5,
CASE WHEN l = 5 THEN parent_type ELSE NULL END AS parent_type_level_5,
CASE WHEN l = 5 THEN parent_id ELSE NULL END AS parent_id_level_5
FROM   (SELECT id,
parent_type,
parent_id,
LEVEL AS l
FROM   dat
START WITH id = 71123
CONNECT BY PRIOR parent_id = id));

这是结果 ID_LEVEL_1|PARENT_TYPE_LEVEL_1|PARENT_ID_LEVEL_1|ID_LEVEL_2|PARENT_TYPE_LEVEL_2|PARENT_ID_LEVEL_2|ID_LEVEL_3|PARENT_TYPE_LEVEL_3|PARENT_ID_LEVEL_3|ID_LEVEL_4|PARENT_TYPE_LEVEL_4|PARENT_ID_LEVEL_4|ID_LEVEL_5|PARENT_TYPE_LEVEL_5|PARENT_ID_LEVEL_5 71123| FSE| 67114| 67114| 夏令时| 67111| 67111| | 0| | | | | |

但请理解，它仅适用于您在问题中所说的 5 个级别。