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

Question

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?

Answer 1

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