--Se relaciona cte_categorias con la categoria del caso por el campo 'Hierarchy'

WITH cte_categories AS (
	SELECT DISTINCT c.ctg_index,
		c.ctg_caption,
		c.ctg_parent,
		1 AS level,
		CONVERT (VARCHAR (MAX), c.ctg_caption) hierarchy
	FROM ASDK_CATEGORY c
	UNION ALL
	SELECT cy.ctg_index,
		cy.ctg_caption,
		cy.ctg_parent,
		level + 1,
		hierarchy + '/' + CONVERT (VARCHAR (MAX), cy.ctg_caption)
	FROM ASDK_CATEGORY cy
	JOIN cte_categories cs ON cs.ctg_index = cy.ctg_parent
), cte_categories2 AS (
	SELECT
		l1.ctg_caption nivel1,
		l2.ctg_caption nivel2,
		l3.ctg_caption nivel3,
		l1.ctg_caption + '.' + l2.ctg_caption + '.' + l3.ctg_caption Hierarchy
	FROM cte_categories l1
	LEFT JOIN cte_categories l2 ON l2.ctg_parent = l1.ctg_index
		AND l2.level = 2
	LEFT JOIN cte_categories l3 ON l3.ctg_parent = l2.ctg_index
		AND l3.level = 3
	LEFT JOIN cte_categories l4 ON l4.ctg_parent = l3.ctg_index
		AND l4.level = 4
	WHERE l1.level = 1
)