sqlsql-serverparent-childrecursive-queryrecursive-cte

Parent Child table with "N" number of levels


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:

enter image description here

The order I need for this data is the following:

enter image description here

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:

enter image description here

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


Solution

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