Common Type Expression Query for performing recursion in circular relationship

prem07it50-22307
Posted by prem07it50-22307 under Sql Server category on | Points: 40 | Views : 813
WITH Managers(EMP_STAFFID, EMP_FullName,REPORTINGTO_ID,LevelNo,Sentinel) AS
(
SELECT EMP_STAFFID, EMP_FullName, REPORTINGTO_ID, 1 AS LevelNo,Sentinel = CAST(REPORTINGTO_ID AS VARCHAR(MAX))
FROM VW_GEN_EMPSEARCH WHERE (REPORTINGTO_ID = 701423)

UNION ALL

SELECT VW_GEN_EMPSEARCH.EMP_STAFFID,VW_GEN_EMPSEARCH.EMP_FullName, VW_GEN_EMPSEARCH.REPORTINGTO_ID, LevelNo + 1,Sentinel + '|' + CAST(m.EMP_STAFFID as varchar)
FROM VW_GEN_EMPSEARCH INNER JOIN Managers AS m ON VW_GEN_EMPSEARCH.REPORTINGTO_ID = m.EMP_STAFFID WHERE CHARINDEX(m.EMP_STAFFID,Sentinel)=0
)


SELECT EMP_STAFFID, EMP_FullName,REPORTINGTO_ID,LevelNo,sentinel from managers order by levelno
OPTION (MAXRECURSION 0)

Comments or Responses

Login to post response