WITH dict_Tree (id,parent_id,FullName)
AS 
(
	SELECT 1,NULL,'Директор'
	UNION ALL
	SELECT 2,1,'Руководитель департамента Алёшин'
	UNION ALL
	SELECT 3,1,'Руководитель департамента Родин'
	UNION ALL
	SELECT 4,3,'Руководитель отдела Петров'
	UNION ALL
	SELECT 5,4,'Хреновый программист'

),
Tree(ID, level, c_path)
as
(

	select id, 1, cast(FullName + ';' AS VARCHAR(MAX))
	from dict_Tree
	where Parent_ID IS  null
	union all
	select
		tr.ID,
		t.level+1,
		t.c_path + tr.FullName + ';'
	from Tree t INNER  JOIN dict_Tree tr ON tr.parent_id = t.id
		
)
SELECT	t.ID, t.LEVEL,
		MAX(CASE WHEN t.N = 1 THEN t.c_value END) v1,
		MAX(CASE WHEN t.N = 2 THEN t.c_value END) v2,
		MAX(CASE WHEN t.N = 3 THEN t.c_value END) v3,
		MAX(CASE WHEN t.N = 4 THEN t.c_value END) v4
FROM (
		SELECT t.ID, t.LEVEL,t.c_path,value  AS c_value,row_number() OVER (PARTITION BY t.ID,t.level ORDER BY (SELECT 1)) N
		  FROM Tree t
			CROSS  APPLY STRING_SPLIT(t.c_path, ';') 
		) t 
WHERE t.c_value <> ''
GROUP BY t.ID, t.LEVEL