3

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?

4

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:

Test Results

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.