SQL - How to parse hierarchy levels from path column

Question

I am trying to represent hierarchical data through an API, and am looking for a solution I could implement on the SQL side (the procedure that is called from the API). I have a column that represents a trace (an 'ID_Path') from the highest parent down to it's children, and am trying to order from an item in the branch downward.

To explain, I have a 'bag' item of ID 215548 that holds 2 direct children, with a child in each beneath. To explain:

ID: 215548

ID_Path: 215548 < 215467 < 215465 < 215428 < 0

So, 215548 is a descendant of the highest parent, 0. The children beneath 215548 are:

ID: 139269

ID_Path: 139269 < 215548 < 215467 < 215465 < 215428 < 0

and

ID: 262379

ID_Path: 262379 < 215548 < 215467 < 215465 < 215428 < 0

From this example, you can see how I display the ID paths and the hierarchical relationship. When I call in the API on 215548 though, I want to see that 215548 has a 'level' of 0 while 139269 and 262379 have a 'level' of 1. I wanted to know how to go about parsing within my procedure to get this kind of result.

In my procedure, I currently have:

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[calculation_table]
@myid INT
AS BEGIN
SELECT * FROM ITEM WHERE ID_Path LIKE '%' + cast(@myid AS nvarchar)  + '%'
END

How can I go about processing levels? Is there a way I can parse from the number of <'s in my path, and then find the difference between that and the number of <'s for my inputted ID?

I appreciate your help. Thank you!

if so, i'd recommend not doing it that way. have your `id` column, and add a `parentId` column that references the `id` of the parent item and then build the hierarchy that way. Then you deal with id values from one of the 2 columns rather than dissecting string values to work it out. — Tanner, Jul 27 '17 at 15:36
take a look at this example: https://stackoverflow.com/a/25551203/57475 — Tanner, Jul 27 '17 at 15:37

score 0 · Answer 1 · edited Sep 23 '21 at 21:54

I'm not 100% clear on what you're after, but hopefully this will get you going down the right path.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL 
BEGIN   -- DROP TABLE #TestData;
    CREATE TABLE #TestData (
        ID INT NOT NULL,
        ID_Path VARCHAR(1000) NOT NULL  
        );
    INSERT #TestData (ID, ID_Path) VALUES 
        (1, '215548 < 215467 < 215465 < 215428 < 0'),
        (2, '139269 < 215548 < 215467 < 215465 < 215428 < 0'),
        (3, '262379 < 215548 < 215467 < 215465 < 215428 < 0');
END;

--=================================================================

DECLARE @ID INT = 215465;

SELECT 
    *
FROM 
    (
    SELECT 
        td.ID,
        td.ID_Path,
        NodeeLevel = ROW_NUMBER() OVER (PARTITION BY td.ID ORDER BY sc.ItemNumber DESC) - 1,
        Child_ID = CAST(sc.Item AS INT),
        Parent_ID = LAG(CAST(sc.Item AS INT), 1) OVER (PARTITION BY td.ID ORDER BY sc.ItemNumber DESC)
    FROM 
        #TestData td
        CROSS APPLY dbo.SplitCSVToTable8K(td.ID_Path, '<') sc
        /* code for splitter function can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ */
    ) x
WHERE 
    x.Child_ID = @ID;

SQL - How to parse hierarchy levels from path column

1 Answers1