WITH segments AS ( SELECT ownerId, fsl_tablecode, clientSegId, parentClientSegId, description as segmentName, 0 AS hierarchyLevel, cast( replace( description, '/', '|' ) as varchar(max)) as segmentPath FROM tbl_OPR_ClientSegments with (nolock) WHERE nullif( parentClientSegId, 0 ) is null and ownerId = and fsl_tablecode = UNION ALL SELECT i.ownerId, i.fsl_tablecode, i.clientSegId, i.parentClientSEgId, i.description as segmentName, c.hierarchyLevel + 1 AS hierarchyLevel, c.segmentPath + '/' + replace( i.description, '/', '|' ) as segmentPath FROM tbl_OPR_ClientSegments i with (nolock) INNER JOIN segments c ON c.clientSegId = i.parentClientSegId and c.ownerId = i.ownerId and c.fsl_tablecode = i.fsl_tablecode ) select clientSegId, parentClientSegId, segmentName, hierarchyLevel, segmentPath from segments order by segmentPath for json auto, include_null_values