CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012hierarchyid
Sharath Chandra
Sharath Chandra

Reputation: 704

Flatten the tree path in SQL server Hierarchy ID

I am using SQL Hierarchy data type to model a taxonomy structure in my application. The taxonomy can have the same name in different levels

enter image description here

During the setup this data needs to be uploaded via an excel sheet.

Before inserting any node I would like to check if the node at a particular path already exists so that I don't duplicate the entries. What is the easiest way to check if the node @ particular absolute path already exists or not?

for e.g Before inserting say "Retail" under "Bank 2" I should be able to check "/Bank 2/Retail" is not existing

Is there any way to provide a flattened representation of the entire tree structure so that I can check for the absolute path and then proceed?

enter image description here

Upvotes: 0

Views: 3307

Answers (2)

Brian
Brian

Reputation: 78

Google brought me here for flattening hierarchyid. Modified JotaBe answer to use hierarchyid.

Sample Data

CREATE TABLE #BankDemo  
(
    Node hierarchyid NOT NULL,  
    Name nvarchar(30) NOT NULL   
);

   INSERT #BankDemo  
    VALUES   
('/1/', 'Bank1'),  
('/2/', 'Bank2'),  
('/1/1/', 'Retail'),  
('/1/1/1/', 'Loans'),  
('/1/1/2/', 'Advances'),  
('/2/1/', 'Retail'),  
('/2/1/1/', 'Agriculture Loans'),  
('/2/1/2/', 'Advances');

Recursive Query

WITH H AS
(
    -- Anchor: the first level of the hierarchy
    SELECT Node, Node.ToString() as Path, Name, CAST(Name AS NVARCHAR(300)) AS FlattenedTreePath 
    FROM #BankDemo 
    WHERE Node.GetAncestor(1) = hierarchyid::GetRoot()      
UNION ALL
    -- Recursive: join the original table to the anchor, and combine data from both  
    SELECT T.Node, T.Node.ToString(), T.Name, CAST(H.FlattenedTreePath + '\' + T.Name AS NVARCHAR(300)) 
    FROM #BankDemo T INNER JOIN H ON  T.Node.GetAncestor(1) = H.Node
)
-- You can query H as if it was a normal table or View
SELECT * FROM H

Results of query

Node Path Name FlattenedTreePath
0x58 /1/ Bank1 Bank1
0x68 /2/ Bank2 Bank2
0x6AC0 /2/1/ Retail Bank2\Retail
0x6AD6 /2/1/1/ Agriculture Loans Bank2\Retail\Agriculture Loans
0x6ADA /2/1/2/ Advances Bank2\Retail\Advances
0x5AC0 /1/1/ Retail Bank1\Retail
0x5AD6 /1/1/1/ Loans Bank1\Retail\Loans
0x5ADA /1/1/2/ Advances Bank1\Retail\Advances

Upvotes: 0

JotaBe
JotaBe

Reputation: 39025

Yes, you can do it using a recursive CTE.

In each iteration of the query you can append a new level of the hierarchy name.

There are lots of examples of this technique on the internet.

For example, with this sample data:

CREATE TABLE Test
(id INT,
parent_id INT null,
NAME VARCHAR(50)
)

INSERT INTO Test VALUES(1, NULL, 'L1')
INSERT INTO Test VALUES(2, 1, 'L1-A')
INSERT INTO Test VALUES(3, 2, 'L1-A-1')
INSERT INTO Test VALUES(4, 2, 'L1-A-2')
INSERT INTO Test VALUES(5, 1, 'L1-B')
INSERT INTO Test VALUES(6, 5, 'L1-B-1')
INSERT INTO Test VALUES(7, 5, 'L1-B-2')

you can write a recursive CTE like this:

WITH H AS
(
    -- Anchor: the first level of the hierarchy
    SELECT id, parent_id, name, CAST(name AS NVARCHAR(300)) AS path 
    FROM Test 
    WHERE parent_id IS NULL      
UNION ALL
    -- Recursive: join the original table to the anchor, and combine data from both  
    SELECT T.id, T.parent_id, T.name, CAST(H.path + '\' + T.name AS NVARCHAR(300)) 
    FROM Test T INNER JOIN H ON T.parent_id = H.id
)
-- You can query H as if it was a normal table or View
SELECT * FROM H
   WHERE PATH = 'L1\L1-A' -- for example to see if this exists

The result of the query (without the where filter) looks like this:

1  NULL  L1      L1
2  1     L1-A    L1\L1-A
5  1     L1-B    L1\L1-B
6  5     L1-B-1  L1\L1-B\L1-B-1
7  5     L1-B-2  L1\L1-B\L1-B-2
3  2     L1-A-1  L1\L1-A\L1-A-1
4  2     L1-A-2  L1\L1-A\L1-A-2

Upvotes: 7

Related Questions