Parent Child Recursive Relation SQL-Server 2008 JDE

 

drop table #temp2

select yaan8,a.ABALPH ChildName, yaanpa,b.ABALPH ParentName into #temp2  from proddta.F060116

left outer join PRODDTA.F0101 a on yaan8=a.aban8

left outer join PRODDTA.F0101 b on yaanpa=b.aban8

where  yapast in('1','0','6') order by 2

go

 

WITH Recursive_CTE AS (

SELECT

  child.YAAN8,

  CAST(child.ChildName as varchar(100)) ChildName,

  child.yaanpa yaanpa,

  CAST(NULL as varchar(100)) ParentUnit,

  CAST('>> ' as varchar(100)) LVL,

  CAST(child.YAAN8 as varchar(100)) Hierarchy,

  1 AS RecursionLevel

FROM #temp2 child

WHERE YAAN8 = 123456

 

UNION ALL

 

SELECT

  child.YAAN8,

  CAST(LVL + child.ChildName as varchar(100)) AS ChildName,

  child.yaanpa,

  parent.ChildName ParentUnit,

  CAST('>> ' + LVL as varchar(100)) AS LVL,

  CAST(Hierarchy + ':' + CAST(child.YAAN8 as varchar(100)) as varchar(100)) Hierarchy,

  RecursionLevel + 1 AS RecursionLevel

FROM Recursive_CTE parent

INNER JOIN #temp2 child ON child.yaanpa = parent.YAAN8

)

SELECT * FROM Recursive_CTE ORDER BY Hierarchy