Flatten the tree path in SQL server Hierarchy ID

Question

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

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?

Answer 1

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