SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[puebles_ciclos_niveles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[config_id] [int] NOT NULL,
	[nivel_padre_id] [int] NOT NULL,
	[activo] [int] NOT NULL,
 CONSTRAINT [PK_puebles_ciclos_niveles] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[puebles_ciclos_niveles_config](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[nombre_nivel] [varchar](max) NULL,
	[orden_nivel] [int] NULL,
	[empresa_id] [int] NULL,
	[proyecto_id] [int] NULL,
	[activo] [int] NULL,
	[usuario_id] [int] NULL,
 CONSTRAINT [PK_puebles_ciclos_niveles_config] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[puebles_ciclos_niveles] ON 
INSERT [dbo].[puebles_ciclos_niveles]
    ([id], [config_id], [nivel_padre_id], [activo])
VALUES
    (1, 1, 0, 1),
    (2, 2, 1, 1),
    (3, 2, 1, 1),
    (4, 3, 2, 1),
    (5, 3, 2, 1),
    (6, 4, 4, 1),
    (7, 4, 4, 1),
    (8, 2, 1, 1),
    (9, 3, 3, 1)
SET IDENTITY_INSERT [dbo].[puebles_ciclos_niveles] OFF
9 rows affected

SET IDENTITY_INSERT [dbo].[puebles_ciclos_niveles_config] ON 
INSERT [dbo].[puebles_ciclos_niveles_config]
    ([id], [nombre_nivel], [orden_nivel], [empresa_id], [proyecto_id], [activo], [usuario_id])
VALUES
    (1, N'Gerente', 1, 16, 2, 1, 6454),
    (2, N'Residente', 2, 16, 2, 1, 6454),
    (3, N'Supervisor', 3, 16, 2, 1, 6454),
    (4, N'Operador', 4, 16, 2, 1, 6454)
SET IDENTITY_INSERT [dbo].[puebles_ciclos_niveles_config] OFF
4 rows affected

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]


id
config_id
nombre_nivel
orden_nivel
nivel_padre_id
empresa_id
proyecto_id
activo_config
usuario_id
activo
path


1
1
Gerente
1
0
16
2
1
6454
1
/1/

2
2
Residente
2
1
16
2
1
6454
1
/1/2/

4
3
Supervisor
3
2
16
2
1
6454
1
/1/2/4/

6
4
Operador
4
4
16
2
1
6454
1
/1/2/4/6/

7
4
Operador
4
4
16
2
1
6454
1
/1/2/4/7/

5
3
Supervisor
3
2
16
2
1
6454
1
/1/2/5/

3
2
Residente
2
1
16
2
1
6454
1
/1/3/

9
3
Supervisor
3
3
16
2
1
6454
1
/1/3/9/

8
2
Residente
2
1
16
2
1
6454
1
/1/8/

id	config_id	nombre_nivel	orden_nivel	nivel_padre_id	empresa_id	proyecto_id	activo_config	usuario_id	activo	path
1	1	Gerente	1	0	16	2	1	6454	1	/1/
2	2	Residente	2	1	16	2	1	6454	1	/1/2/
4	3	Supervisor	3	2	16	2	1	6454	1	/1/2/4/
6	4	Operador	4	4	16	2	1	6454	1	/1/2/4/6/
7	4	Operador	4	4	16	2	1	6454	1	/1/2/4/7/
5	3	Supervisor	3	2	16	2	1	6454	1	/1/2/5/
3	2	Residente	2	1	16	2	1	6454	1	/1/3/
9	3	Supervisor	3	3	16	2	1	6454	1	/1/3/9/
8	2	Residente	2	1	16	2	1	6454	1	/1/8/