Populate object hierarchy in an additional column

• girish.nehte-1109699

  SSC Veteran

  Points: 288

  More actions

  July 29, 2013 at 12:14 am

  #296019

  Hi,

  In my database I have a table "ObjectHierarchy" having following columns.

  ObjectID [Primary Key]

  ObjectName

  ParentObjectID [ObjectID from the same table]

  Now in this table what I want it to have an ObjectPath column that will show the hierarchy of a particular object.

  For egample

  ObjectID ObjectName ParentObjectID ObjectPath

  1 AAA -1

  2 BBB -1

  3 CCC 1 AAA

  4 DDD 3 AAA/CCC

  5 EEE 4 AAA/CCC/DDD

  6 FFF 2 BBB

  ObjectPath column should contain the path of the object but not the actual objectname. I want to add a fucntion so that when I run the query

  ALTER TABLE ObjectHierarchy

  ADD ObjectPath AS FN_ObjectPath(ObjectID)

  this will add a column with the respective object path. How that function should be?

  Regards,

  Girish

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  July 29, 2013 at 2:05 am

  #1636541

  This blog post explains the concept on how you can achieve this using a recursive CTE:

  Using recursive CTE for a hierarchical relationship[/url]

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP