GO
DECLARE @v_hierarchy_separator varchar;
SET @v_hierarchy_separator= '\';

BEGIN
WITH cte_categories AS (SELECT DISTINCT c.grou_id grou_id,
						c.grou_name grou_name,
						c.grou_parent grou_parent,
						'' fl_str_hint,
						1 AS level
					FROM AFW_GROUPS c 
					UNION ALL
					SELECT  cy.grou_id grou_id,
						cy.grou_name grou_name,
						cy.grou_parent grou_parent,
						'' fl_str_hint,
						level + 1
					FROM AFW_GROUPS cy
					JOIN cte_categories cs ON cy.grou_id = cs.grou_parent 
					WHERE cy.grou_parent <> cy.grou_id
					),
				cte_level1 AS (
					SELECT DISTINCT grou_id,
						grou_parent,
						fl_str_hint,
						grou_name level1
					FROM cte_categories
					WHERE level = 1
				),
				cte_level2 AS (
					SELECT DISTINCT grou_id,
						grou_parent,
						fl_str_hint,
						grou_name + @v_hierarchy_separator level2
					FROM cte_categories
					WHERE level = 2
				),
				cte_level3 AS (
					SELECT DISTINCT grou_id,
						grou_parent,
						fl_str_hint,
						grou_name + @v_hierarchy_separator level3
					FROM cte_categories
					WHERE level = 3
				),
				cte_level4 AS (
					SELECT DISTINCT grou_id,
						grou_parent,
						fl_str_hint,
						grou_name + @v_hierarchy_separator level4
					FROM cte_categories
					WHERE level = 4
				),
				cte_level5 AS (
					SELECT DISTINCT grou_id,
						grou_parent,
						fl_str_hint,
						grou_name + @v_hierarchy_separator level5
					FROM cte_categories
					WHERE level = 5
				)--,
				--cte_level6 AS (
				--	SELECT DISTINCT grou_id,
				--		grou_parent,
				--		fl_str_hint,
				--		grou_name + @v_hierarchy_separator level6
				--	FROM cte_categories
				--	WHERE level = 6
				--),
				--cte_level7 AS (
				--	SELECT DISTINCT grou_id,
				--		grou_parent,
				--		fl_str_hint,
				--		grou_name + @v_hierarchy_separator level7
				--	FROM cte_categories
				--	WHERE level = 7
				--),
				--cte_level8 AS (
				--	SELECT DISTINCT grou_id,
				--		grou_parent,
				--		fl_str_hint,
				--		grou_name + @v_hierarchy_separator level8
				--	FROM cte_categories
				--	WHERE level = 8
				--),
				--cte_level9 AS (
				--	SELECT DISTINCT grou_id,
				--		grou_parent,
				--		fl_str_hint,
				--		grou_name + @v_hierarchy_separator level9
				--	FROM cte_categories
				--	WHERE level = 9
				--),
				--cte_level10 AS (
				--	SELECT DISTINCT grou_id,
				--		grou_parent,
				--		fl_str_hint,
				--		grou_name + @v_hierarchy_separator level10
				--	FROM cte_categories
				--	WHERE level = 10
				--)


				SELECT  
	
					CASE
						WHEN grou_name = grou_name THEN 'TODOS'
					END AS PATH,
					CASE
						WHEN grou_id = grou_id THEN '0'
					END AS [ID GROUP]

				FROM AFW_GROUPS A

				WHERE grou_id = 1

				UNION ALL

				SELECT 
				CASE
					WHEN LEN(ctg_hierarchy) > 30 THEN '(...)' + SUBSTRING(ctg_hierarchy, LEN(ctg_hierarchy) -30, LEN(ctg_hierarchy))
					ELSE ctg_hierarchy
				END AS PATH,
				level1 AS [ID GROUP]

				FROM (SELECT DISTINCT l1.grou_id
					level1, level2, level3, level4, level5,-- level6, level7, level8, level9, level10,   
					CASE WHEN level5 IS NOT NULL THEN '(...)' ELSE '' END
					+ ISNULL(level4, '')
					+ ISNULL(level3, '') + ISNULL(level2, '') + ISNULL(level1, '') ctg_hierarchy,
					COALESCE(COALESCE(l1.fl_str_hint, l2.fl_str_hint, l3.fl_str_hint), l4.fl_str_hint, l5.fl_str_hint/*, l6.fl_str_hint, l7.fl_str_hint, l8.fl_str_hint, l9.fl_str_hint*/) fl_str_hint

					
				FROM cte_level1 l1
				LEFT JOIN cte_level2 l2 ON l2.grou_id = l1.grou_parent
				LEFT JOIN cte_level3 l3 ON l3.grou_id = l2.grou_parent
				LEFT JOIN cte_level4 l4 ON l4.grou_id = l3.grou_parent
				LEFT JOIN cte_level5 l5 ON l5.grou_id = l4.grou_parent
				--LEFT JOIN cte_level6 l6 ON l6.grou_id = l5.grou_parent
				--LEFT JOIN cte_level7 l7 ON l7.grou_id = l6.grou_parent
				--LEFT JOIN cte_level8 l8 ON l8.grou_id = l7.grou_parent
				--LEFT JOIN cte_level9 l9 ON l9.grou_id = l8.grou_parent
				--LEFT JOIN cte_level10 l10 ON l10.grou_id = l9.grou_parent
				) AS HIERARCHY

END