Свести иерархию списка смежности к списку всех путей
У меня есть таблица, которая хранит иерархическую информацию, используя модель списка смежности. (использует самоссылочный ключ - пример ниже. Эта таблица может выглядетьзнакомый):
category_id name parent
----------- -------------------- -----------
1 ELECTRONICS NULL
2 TELEVISIONS 1
3 TUBE 2
4 LCD 2
5 PLASMA 2
6 PORTABLE ELECTRONICS 1
7 MP3 PLAYERS 6
8 FLASH 7
9 CD PLAYERS 6
10 2 WAY RADIOS 6
Каков наилучший метод для «сглаживания» вышеуказанных данных в нечто подобное?
category_id lvl1 lvl2 lvl3 lvl4
----------- ----------- ----------- ----------- -----------
1 1 NULL NULL NULL
2 1 2 NULL NULL
6 1 6 NULL NULL
3 1 2 3 NULL
4 1 2 4 NULL
5 1 2 5 NULL
7 1 6 7 NULL
9 1 6 9 NULL
10 1 6 10 NULL
8 1 6 7 8
Каждая строка - это один «Путь» через Иерархию, за исключением того, что есть строка длякаждый узел (не только каждыйлистовой узел). Столбец category_id представляет текущий узел, а столбцы "lvl" - его предки. Значение для текущего узла также должно быть в самом дальнем правом столбце lvl. Значение в столбце lvl1 всегда будет представлять корневой узел, значения в lvl2 всегда будут представлять прямых потомков lvl1 и т. Д.
Если возможно, метод для генерации этого вывода будет в SQL и будет работать для n-уровневых иерархий.