Untitled

WITH Hierarchy(Region, RegionName, ParentRegion, HLevel, z)AS(SELECT Region, RegionName, ParentRegion, 0 as HLevel, Cast('' as varchar(8000)) as z FROM Region Where ParentRegion = 0UNION ALLSELECT SubRegion.Region,SubRegion.RegionName,SubRegion.ParentRegion,HLevel + 1,z + (Case When HLevel = 0 Then ParentRegion.RegionName + '.' Else '' End) + SubRegion.RegionName + '.'FROM Region SubRegion INNER JOIN Hierarchy ParentRegion ON SubRegion.ParentRegion = ParentRegion.Region)SELECT      Region,             RegionName = Replicate('.', HLevel) + RegionName,             ParentRegion,            HLevel,            (Case When z = '' Then RegionName Else z End) As ZFROM  HierarchyOrder by Z