Нужен SQL-запрос для иерархических данных в плоском результирующем наборе

#sql #oracle #pivot #recursive-query

#sql #Oracle #сводный #рекурсивный запрос

Вопрос:

Есть таблица: PS_POSITION_TBL

 position_nbr      reports_to              descr        
 038              143                     DIRECTOR 
 0418             143                     ADVISOR                        
 114              143                     DG                              
 346              114                     Manager                        
 202              114                     Lead

Идея, которую мы хотим построить иерархию выше

случай 1: 038, 0418 и 114 сообщает о 143

случай 2: 346 и 202 отчетов для 114

итак, самый высокий уровень — level1, который равен 143, затем 038,0418,114 — уровень 2, поскольку они отчитываются перед уровнем 1, затем 346 и 202 — уровень 3, и они попадают под сегмент уровня 3, и они отчитываются перед уровнем 2

Они могут достигать уровня 8 (МАКСИМУМ)

Что мне нужно, так это запрос для получения данных в приведенном ниже формате в Oracle:

 level1  level2   level3  level4 level5 level6 level7 level8      descr
 143                                                             CEO            
           038                                                   DIRECTOR        
           0418                                                  ADVISOR         
           114                                                   DG              
                    346                                          Manager         
                    202                                          Lead

Ответ №1:

 select    level1,level2,level3,level4,level5,level6,level7,level8,descr

from     (select        level as n,position_nbr as id,descr,position_nbr

          from          (         select position_nbr ,reports_to ,descr from PS_POSITION_TBL 
                        union all select 143          ,null       ,'CEO' from dual
                        )  t

          start with    reports_to is null

          connect by    reports_to = prior position_nbr
          ) 
          pivot (max(position_nbr) for n in (1 as level1,2 as level2,3 as level3,4 as level4,5 as level5,6 as level6,7 as level7,8 as level8))
;

или

 select    decode (n,1,position_nbr) as level1
         ,decode (n,2,position_nbr) as level2
         ,decode (n,3,position_nbr) as level3
         ,decode (n,4,position_nbr) as level4
         ,decode (n,5,position_nbr) as level5
         ,decode (n,6,position_nbr) as level6
         ,decode (n,7,position_nbr) as level7
         ,decode (n,8,position_nbr) as level8
         ,descr

from     (select          level as n,position_nbr,descr

          from          (         select position_nbr ,reports_to ,descr from PS_POSITION_TBL 
                      union all select 143          ,null       ,'CEO' from dual
                      )  t

          start with    reports_to is null

          connect by    reports_to = prior position_nbr
          ) 
;

1. Это правильная идея, но она жестко кодирует 143 вместо того, чтобы находить его по данным. 143 можно найти, например, выбрав distinct из reports_to, которые не находятся в position_nbr.

2. @mathguy, ты просто догадываешься, как и я. Вы не знаете, что 143 не находится в position_nbr (и вы не получите описание, подобное этому)

3. Если 143 находится в position_nbr, то это не уровень 1, не так ли? В этом весь смысл! И описание следует оставить нулевым; то, что OP показывает на выходе, нигде не содержится во входных данных или в требовании проблемы. (Это можно было бы поместить в последнее, например, «для идентификаторов уровня 1, у которых нет описания, введите жесткий код «CEO» для описания».)

4. @mathguy, совсем нет. 143 может находиться в position_nbr и на уровне 1, если reports_to, например, равен НУЛЮ. Поскольку эта проблема неясна, я представил решение, которое решает вопрос в соответствии с заданными данными.

5. @jujadhav, это отвечает на твой вопрос?

Ответ №2:

Следующий запрос предоставит вам иерархический набор, дополненный слева символом LEVEL :

 SELECT LPAD(' ', level * 2, ' ') || TO_CHAR(position_nbr), descr
  FROM PS_POSITION_TBL
CONNECT BY PRIOR position_nbr = reports_to
START WITH reports_to IS NULL;

Если вас не устраивает отображение уровня в одном столбце ( LEVEL ) и вам нужен столбец для каждого уровня, вы можете использовать PIVOT на основе LEVEL , но это затруднит упорядочивание.