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

Question

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

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

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

Answer 1

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

Может использоваться как JOIN, но также вернуть дополнительные записи, чтобы дать вам NULLs.

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 чрезвычайно выигрывает от нормализованных структур (ваших исходных данных).