December 13, 2010 at 3:51 am
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
December 13, 2010 at 4:08 am
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
December 13, 2010 at 4:20 am
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
December 13, 2010 at 5:22 am
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
December 13, 2010 at 11:31 pm
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply