Jerarquía de lista de adyacencia plana a una lista de todas las rutas
Tengo una tabla que almacena información jerárquica utilizando el modelo de lista de adyacencia. (utiliza una clave autorreferencial: ejemplo a continuación. Esta tabla puede verse enfamilia):
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
Cuál es el mejor método para "aplanar" los datos anteriores en algo como esto?
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
Cada fila es una "Ruta" a través de la Jerarquía, excepto que hay una fila para cada nodo (no solo cadaleaf node). La columna category_id representa el nodo actual y las columnas "lvl" son sus antepasados. El valor para el nodo actual también debe estar en la columna lvl más a la derecha. El valor en la columna lvl1 siempre representará el nodo raíz, los valores en lvl2 siempre representarán descendientes directos de lvl1, y así sucesivamente.
Si es posible, el método para generar esta salida estaría en SQL y funcionaría para jerarquías de n niveles.