Parent Child table with "N" number of levels

Question

This query:

SELECT pcn.id,
       pcn.config_id,
       pcnc.nombre_nivel,
       pcnc.orden_nivel,
       pcn.nivel_padre_id,
       pcnc.empresa_id,
       pcnc.proyecto_id,
       pcnc.activo AS activo_config,
       pcnc.usuario_id,
       pcn.activo
FROM puebles_ciclos_niveles pcn
JOIN puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id

Provides these results:

The order I need for this data is the following:

In here, "nivel_padre_id" represents the parent ID.

I've tried making it with a CTE table, the problem is that the CTE table only adresses one level like so:

WITH RecursiveCTE AS (
SELECT
    pcn.id, pcn.config_id, pcnc.nombre_nivel, pcnc.orden_nivel,pcn.nivel_padre_id,
    pcnc.empresa_id, pcnc.proyecto_id, pcnc.activo AS activo_config, pcnc.usuario_id,pcn.activo
FROM
    puebles_ciclos_niveles pcn
JOIN
    puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
WHERE
    pcn.nivel_padre_id = 0 -- Selecting the root level

UNION ALL

SELECT
    pcn.id, pcn.config_id, pcnc.nombre_nivel, pcnc.orden_nivel, pcn.nivel_padre_id, pcnc.empresa_id, pcnc.proyecto_id,
    pcnc.activo AS activo_config, pcnc.usuario_id, pcn.activo
FROM
    puebles_ciclos_niveles pcn
JOIN
    puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
JOIN
    RecursiveCTE rc ON pcn.nivel_padre_id = rc.id
)

    SELECT
        id, config_id, nombre_nivel, orden_nivel,
        nivel_padre_id, empresa_id, proyecto_id,
        activo_config, usuario_id, activo
    FROM
    RecursiveCTE

This CTE query produces the following:

Is there any way to get the desired result? (Parent - Child(1) - Child (2) - Child(3) - Children (4) -Stop if no more children are found, go back to process the previous level-)

CREATE AND INSERT SCRIPTS

Answer 1

Because you want a specific ordering in your result set, you need to provide an order by clause. But what to order it by? Here's your query with the addition of one column - path which represents the path from the root node to that particular row.

WITH RecursiveCTE AS (
SELECT
    pcn.id, pcn.config_id, pcnc.nombre_nivel, pcnc.orden_nivel,pcn.nivel_padre_id,
    pcnc.empresa_id, pcnc.proyecto_id, pcnc.activo AS activo_config, pcnc.usuario_id,pcn.activo,
    [path] = cast(concat('/', pcn.id, '/' ) as varchar(1000))
FROM
    puebles_ciclos_niveles pcn
JOIN
    puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
WHERE
    pcn.nivel_padre_id = 0 -- Selecting the root level

UNION ALL

SELECT
    pcn.id, pcn.config_id, pcnc.nombre_nivel, pcnc.orden_nivel, pcn.nivel_padre_id, pcnc.empresa_id, pcnc.proyecto_id,
    pcnc.activo AS activo_config, pcnc.usuario_id, pcn.activo,
    [path] = cast(concat(rc.[path], pcn.id, '/' ) as varchar(1000))
FROM
    puebles_ciclos_niveles pcn
JOIN
    puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
JOIN
    RecursiveCTE rc ON pcn.nivel_padre_id = rc.id
)

    SELECT
        id, config_id, nombre_nivel, orden_nivel,
        nivel_padre_id, empresa_id, proyecto_id,
        activo_config, usuario_id, activo,
        [path]
    FROM
    RecursiveCTE
    order by [path];

By way of exposition, the path for the base element is just that row's id, surrounded by slashes. For subsequent/recursive elements, I take the parent's path, append this row's id to it, and end it with another slash. Here's a db fiddle with a demonstration.

Note - the choice of formatting for the path is not arbitrary. SQL Server has a data type called hierarchyid that you can use to get this sort of behavior much less expensively than calculating it dynamically at query time (with the overhead of needing to maintain it on data mutation). The path format that I used is directly castable to hierarchyid should you choose to use it.