Untitled

DECLARE @work_item_id_inicial INT;
DECLARE @board_id_inicial INT;
DECLARE @item_id_inicial INT

SET @item_id_inicial = 1;
SET @work_item_id_inicial = 182948;
SET @board_id_inicial = 376;

WITH RECURSIVA AS (
	SELECT
	  @item_id_inicial AS nivel,
	  dir.directory_name AS directory_name_nivel_1,
	  CAST('' AS NVARCHAR(800)) as directory_name_nivel_2,
	  CAST('' AS NVARCHAR(800)) as directory_name_nivel_3,
	  dir.work_item_id,
	  dir.directory_id,
	  dir.father_directory_id,
	  dir.board_id,
	  arc.archive_id,
	  arc.archive_original_name,
	  arc.archive_azure_name,
	  arc.archive_ocean_url_description,
	  arc.archive_type
	FROM oce_files_directories dir

	INNER JOIN oce_files_directories_archives dia
	ON dir.directory_id = dia.directory_id

	INNER JOIN oce_files_archives arc
	ON dia.archive_id = arc.archive_id

	WHERE dir.work_item_id = @work_item_id_inicial
	AND dir.board_id = @board_id_inicial

    UNION ALL

	SELECT
	  rec.nivel + 1 AS nivel,
      rec.directory_name_nivel_1,
	  case
	    when rec.nivel + 1 = 2 then dir.directory_name
		when rec.nivel + 1 = 3 then rec.directory_name_nivel_2
	    else ''
	  end as directory_name_nivel_2,
	  case
	    when rec.nivel + 1 = 2 then ''
		when rec.nivel + 1 = 3 then dir.directory_name
		else ''
	  end as directory_name_nivel_3,
	  dir.work_item_id,
	  dir.directory_id,
	  dir.father_directory_id,
	  dir.board_id,
	  arc.archive_id,
	  arc.archive_original_name,
	  arc.archive_azure_name,
	  arc.archive_ocean_url_description,
	  arc.archive_type
	FROM oce_files_directories dir

	INNER JOIN RECURSIVA rec
	ON dir.father_directory_id = rec.directory_id
	AND dir.board_id = rec.board_id

	INNER JOIN oce_files_directories_archives dia
	ON dir.directory_id = dia.directory_id

	INNER JOIN oce_files_archives arc
	ON dia.archive_id = arc.archive_id
)
SELECT DISTINCT *
FROM RECURSIVA;