Разделить данные по уровням в иерархии

Пример исходных данных:

| ID   |  ParentID  |
|------|------------|
|  1   |    NULL    |
|  2   |     1      |
|  3   |     1      |
|  4   |     2      |
|  5   |    NULL    |
|  6   |     2      |
|  7   |     3      |

В моих исходных данных у меня есть идентификатор элемента и его родительский идентификатор. У некоторых элементов есть родитель, у некоторых нет, у некоторых есть родитель, а у его родителя есть родитель.

Максимальное количество уровней в этой иерархии равно 3.

Мне нужно получить эту иерархию по уровням.

Lvl 1 - элементы без родителей Lvl 2 - элементы с родителем, у которого нет родителя Lvl 3 - элементы с родителем, у которого тоже есть родитель.

Ожидаемый результат выглядит так:

| Lvl1  |   Lvl2   |   Lvl3   |
|-------|----------|----------|
|  1    |   NULL   |   NULL   |
|  1    |    2     |   NULL   |
|  1    |    3     |   NULL   |
|  1    |    2     |    4     |
|  5    |   NULL   |   NULL   |
|  1    |    2     |    6     |
|  1    |    3     |    7     |

Как я могу это сделать?


sql sql-server sql-server-2017
person Dmitry    schedule 11.10.2018    source источник
comment
Вы знаете для наверняка, что вам нужно перейти на максимальную глубину lvl3, и это не изменится? Если он гибкий/динамический/переменный, то это, как правило, не очень хорошая идея, и возникает вопрос; почему вы хотите это сделать, для чего вы будете это использовать? Вероятно, есть лучшие альтернативы, SQL нравится нормализованная структура, с которой вы начинаете.   -  person MatBailie    schedule 11.10.2018
comment
@MatBailie Мне нужно это представление только для отчета, да, максимальная глубина не изменится и всегда будет 3.   -  person Dmitry    schedule 11.10.2018
comment
Даже для репортажей это часто неправильное направление. Возможно, вам будет лучше задать вопрос о том, как написать пример отчета. Покажите нам данные, с которых вы начнете, покажите нам окончательный отчет, который вы хотите, расскажите нам, какое приложение отчетности вы будете использовать, и тогда мы сможем помочь найти подходящий шаблон, а не просто показать вам, как реализовать то, что часто антипаттерн.   -  person MatBailie    schedule 11.10.2018

Ответы (1)


arrow_upward
3
arrow_downward

Для фиксированного отдела из трех вы можете использовать CROSS APPLY.

Его можно использовать как JOIN, но также возвращать дополнительные записи, чтобы дать вам NULL.

SELECT
  Lvl1.ID   AS lvl1,
  Lvl2.ID   AS lvl2,
  Lvl3.ID   AS lvl3
FROM
  initial_data   AS Lvl1
CROSS APPLY
(
   SELECT ID FROM initial_data WHERE ParentID = Lvl1.ID
   UNION ALL
   SELECT NULL AS ID
)
  AS Lvl2
CROSS APPLY
(
   SELECT ID FROM initial_data WHERE ParentID = Lvl2.ID
   UNION ALL
   SELECT NULL AS ID
)
  AS Lvl3
WHERE
  Lvl1.ParentID IS NULL
ORDER BY
  Lvl1.ID,
  Lvl2.ID,
  Lvl3.ID

Но, согласно моему комментарию, это часто является признаком того, что вы идете по маршруту, отличному от sql. Может показаться, что с этого проще начать, но позже он переворачивается и кусает вас, потому что SQL очень выигрывает от нормализованных структур (ваших исходных данных).

person MatBailie    schedule 11.10.2018
comment
Спасибо, это работает, но также большое спасибо за подробное объяснение плохой практики этого решения. - person Dmitry; 11.10.2018