Display parent-child relationship in one row

• gayakr

  Old Hand

  Points: 382

  More actions

  April 21, 2011 at 2:57 pm

  #238987

  Team,

  I have a table with the following data showing parent-child relationship

  AccountID Parent ID

  -------------------

  1 2

  2 3

  3

  I need a query to list the hierarchy in the following format, indicating that 2 is the parnet of 1 and 3 is the parent of 2, in one row. Any ideas, please. Thanks a lot.

  Result

  ----------

  1 2 3

• Patrick_Fiedler

  SSCommitted

  Points: 1904

  More actions

  April 21, 2011 at 3:29 pm

  #1314736

  Hi,

  you can use a common table expression CTE like:

  ;with cte_Childs(

  RelationString,

  AccountID

  )as(

  select cast(r.AccountID as nvarchar),

  r.AccountID

  from RelationShipTable r

  left join

  RelationShipTable t on r.ParentID=t.AccountID

  where t.AccountID is null

  union all

  select c.RelationString + ' ' + cast(r.AccountID as nvarchar),

  r.AccountID

  from cte_Childs c

  inner join

  RelationShipTable r on c.AccountID = r.ParentID

  )

  select max(RelationString)

  from cte_Childs

  [font="Arial"]Kind regards,

  Patrick Fiedler
  Consultant

  Fiedler SQL Consulting

  www.fiedler-sql-consulting.de
  [/font]