snippetsqlMinor

Create a hierarchy chain (reverse path) for each node in a tree

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

patheachreversecreatehierarchynodefortreechain

Problem

Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server.

Given:

parent  child   depth
1       1       0
2       2       0
3       3       0
1       2       1
2       3       1
1       3       2

I'd like:

id    path
1     1
2     1,2
3     1,2,3

Does that make sense?

Solution

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg

CREATE TABLE #heredity
(
    parent  INT NOT NULL,
    child   INT NOT NULL,
    depth   INT NOT NULL,

CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child )
);
GO

INSERT INTO #heredity ( parent, child, depth )
VALUES
    ( 1, 1, 0 ),
    ( 2, 2, 0 ),
    ( 3, 3, 0 ),
    ( 1, 2, 1 ),
    ( 2, 3, 1 ),
    ( 1, 3, 2 );
GO

SELECT  
    child,
    STUFF(
    (
    SELECT ',' + CAST( parent AS VARCHAR(20) )
    FROM #heredity p
    WHERE c.parent = p.child
    ORDER BY parent DESC
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(100)'),
    1, 1,'') AS [path]
FROM #heredity c
WHERE depth = 0;

My results:

Code Snippets

CREATE TABLE #heredity
(
    parent  INT NOT NULL,
    child   INT NOT NULL,
    depth   INT NOT NULL,

CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child )
);
GO

INSERT INTO #heredity ( parent, child, depth )
VALUES
    ( 1, 1, 0 ),
    ( 2, 2, 0 ),
    ( 3, 3, 0 ),
    ( 1, 2, 1 ),
    ( 2, 3, 1 ),
    ( 1, 3, 2 );
GO


SELECT  
    child,
    STUFF(
    (
    SELECT ',' + CAST( parent AS VARCHAR(20) )
    FROM #heredity p
    WHERE c.parent = p.child
    ORDER BY parent DESC
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(100)'),
    1, 1,'') AS [path]
FROM #heredity c
WHERE depth = 0;

Context

StackExchange Database Administrators Q#139514, answer score: 4

Revisions (0)

No revisions yet.