WITH dst as ( select distinct parentDstId as dstId, parentDstName as dstName, 'PARENT' dstType, 0 parentDstId, '' as parentDstName, 0 as territoryId, cast( null as varchar ) as territoryName, cast( null as varchar ) as territoryPath from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and parentDstId > 0 and parentDstId != dstId and operatorPurchasing = 'Y' union all select distinct dstId, dstName, dstType, 0 parentDstId, cast( null as varchar ) as parentDstName, territoryId, territoryName, territoryPath from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and dstType = 'INDEPENDENT' and operatorPurchasing = 'Y' union all select distinct dstId, dstName, dstType, parentDstId, parentDstName, territoryId, territoryName, territoryPath from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and dstType = 'BRANCH' and operatorPurchasing = 'Y' ), cte as ( select dst.dstId, dst.dstName, dst.parentDstId, dst.parentDstName, dst.dstType, cast( dst.dstName as varchar(max)) as dstPath, 0 as dstLevel, dst.territoryId, dst.territoryName, dst.territoryPath from dst with (nolock) where dst.dstType in ( 'PARENT','INDEPENDENT' ) union all select dst.dstId, dst.dstName, dst.parentDstId, dst.parentDstName, dst.dstType, cast( cte.dstName + '/' + dst.dstName as varchar(max)) as dstPath, cte.dstLevel + 1 as dstLevel, dst.territoryId, dst.territoryName, dst.territoryPath from dst with (nolock) inner join cte on cte.dstId = dst.parentDstId where dst.dstType = 'BRANCH' ), data as ( select dstId, dstName, cte.territoryId, cte.territoryName, dstPath, case when dstType = 'PARENT' then 'parentDstId:' else 'dstId:' end + convert( varchar, dstId ) as keyId, cte.territoryPath from cte ) #forJSON( 'data', attributes.datastore, 'dstPath' )#