层次查询其给出输出中的多个行，要在一个行输出

问题描述：

我有一个分层表，我取的分层数据，但其以行来，如下所示：表结构：层次查询其给出输出中的多个行，要在一个行输出

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

它不清楚你想要什么？ –

kya apko ye html me show karna hai？ –

你需要['pivot']（http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#CHDFAFIE），但是你知道你可能需要处理多少个关卡？也许你有一个报告或应用程序层可以做到这一点，而不是显示。 –

答

可能看起来太复杂了，但其实很简单。

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));

但要明白，它只会像你在你的问题中陈述的5级。

层次查询其给出输出中的多个行，要在一个行输出

相关推荐