balakishore nadella balakishore nadella - 7 months ago 28
SQL Question

Getting final parent in hierarchy table

I have a SQL table with parent and child information like

child parent
a b
b c
c d
e f

My result should be like

child parent
a d
b d
c d
e f

Each row should have child and its final parent in its hierarchy.

How can I do that in SQL Server?


Yes, it is possible to do using recursive CTE. This is a sample.

declare @tbl table(child varchar(5),  parent varchar(5))
insert @tbl values
  ('a',      'b'),  
  ('b',      'c'),
  ('c',      'd'),
  ('e',      'f')

  ;with tbl as (
  --anchor query
  --find starting level
  select parent child, parent parent, 0 lvl
  from @tbl t
  where parent not in (select child from @tbl)
  union all
  --recursive query
  select t.child, tbl.parent, lvl+1 --keep top level parent
  from @tbl t
  inner join tbl on t.parent=tbl.child --go up the tree to previous level
  --filter final results
  select child,parent from tbl
  where lvl>0 --lvl 0 is fake level
  order by child