Create a hierarchy chain (reverse path) for each node in a tree
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: