Data in a Tree

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  December 13, 2010 at 3:51 am

  #388425

  Hi,

  I have two tables. Table 1 "#tempNodes" which holds a unique list of Nodes. Table 2 "#tempTree" I have a TreePath which is made up of multiple nodes and then an Item at that level of the Tree.

  I need help to get the NodeName from the Nodes Table but am not sure how to go about doing this ...

  In the code below I would like to have the TreePath (e.g. 100/200/400) with TreeDescription ( Client/Bank/Compliance )

  All help and suggestions will be much appreciated

  G

  --Node Table:

  CREATE TABLE #tempNodes

  (

  NodeID INT

  , NodeName VARCHAR(50)

  )

  --Populate Node Table

  INSERT INTO #tempNodes

  SELECT 100, 'Client'

  UNION ALL

  SELECT 200, 'Bank'

  UNION ALL

  SELECT 300, 'Personal'

  UNION ALL

  SELECT 400, 'Compliance'

  UNION ALL

  SELECT 500, 'Contact'

  --Tree Table

  CREATE TABLE #tempTree

  (TreePath VARCHAR(400)

  , Item Varchar(50)

  )

  --Populate Tree Table

  INSERT INTO

  #tempTree

  SELECT

  '100/200/400', 'KYC'

  UNION ALL

  SELECT

  '300/500', 'Meeting Minutes'

  --Data:

  SELECT

  TreePath

  , '???' AS TreePathDescription

  , Item

  FROM

  #tempTree

  --CLEAN UP

  DROP TABLE #tempTree

  DROP TABLE #tempNodes

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 13, 2010 at 4:08 am

  #1261600

  OK, so your Treepath column contains a delimited list that you need to split into its individual components? You need a function to do that for you. If you search this site, you should find plenty of examples of code that will create the function.

  John

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  December 13, 2010 at 4:20 am

  #1261607

  Well, probably not the fastest solution you can get, but maybe this on will do.

  SELECT

  T.TreePath,

  STUFF(

  (SELECT

  ',' + N.NodeName

  FROM

  #tempNodes N

  WHERE

  '/' + T.TreePath + '/' LIKE '%/' + CAST(N.NodeID AS VARCHAR(10)) + '/%'

  FOR XML PATH(''), TYPE).value('./text()[1]', 'VARCHAR(8000)'),

  1, 1, '') AS TreePathDescription,

  T.Item

  FROM

  #tempTree T

  Peter

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  December 13, 2010 at 5:22 am

  #1261629

  As John already pointed out, it is probably a better idea to split the treepath into individual nodes if you have a lot of different nodes. Here is some sample code using the function DelimitedSplit8K which can be found here.

  SELECT

  T.TreePath,

  STUFF(

  (SELECT

  ',' + N.NodeName

  FROM

  #tempNodes N

  CROSS APPLY

  dbo.DelimitedSplit8K(T.TreePath, '/') S

  WHERE

  N.NodeID = S.Item

  FOR XML PATH(''), TYPE).value('./text()[1]', 'VARCHAR(8000)'),

  1, 1, '') AS TreePathDescription,

  T.Item

  FROM

  #tempTree T

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  December 13, 2010 at 11:31 pm

  #1262042

  Thank you both for taking time to look at my problem.

  Peter the code and link to the function have been incredibly helpful and have opened up a new avenue for me to answer future problems, thanks again!