CREATE TABLE dwh.DimOrganization(
	Org_Id int  NOT NULL, -- IDENTITY(1,1)
	Parent_Org_Id int NULL,
	Src_Org_ID uniqueidentifier NOT NULL,
	Org_Name nvarchar(500) NOT NULL,
	Org_level tinyint  NOT NULL,
,CONSTRAINT PK_DimOrganization PRIMARY KEY    (	Org_Id  )
)

With CTE
AS
(
SELECT Org_Id,Parent_Org_Id,Org_Name, 1 AS level,CAST(Org_Name AS varchar(max)) AS Org_Path
FROM dwh.DimOrganization 
WHERE 
Parent_Org_Id is Null --  Roots Org_Id = Parent_Org_Id 
UNION ALL
SELECT e.Org_Id,e.Parent_Org_Id,e.Org_Name,c.Level + 1
, CAST(c.Org_Path + '/' + e.Org_Name AS varchar(max))
FROM CTE c
INNER JOIN dwh.DimOrganization  e
	ON e.Parent_Org_Id = c.Org_Id 
WHERE 
1=1 -- e.Parent_Org_Id <> e.Org_Id
AND c.level < 5 -- THIS PREVENTS THE RECURSION FROM RUNNING
)
Select * From CTE
ORDER BY level , Org_Id