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/ |