How can I create a closure table using data from an adjacency list?

I was trying to figure out the same thing, but wanted it in a recursive CTE. This wouldn't have worked for you (SQL Server 2008+), but here's what I ended up with for anyone else looking.

The key is that the anchors aren't your root nodes (where parent_id IS NULL), but instead all your zero depth rows-to-be in the closure table.

Table

CREATE TABLE dbo.category (    id         INT IDENTITY(1, 1) NOT NULL,    parent_id  INT                    NULL)

Data

INSERT INTO dbo.category (id, parent_id)VALUES    (1, NULL),    (2, 1),    (3, 1),    (4, 2)

CTE

WITH category_cte AS(    SELECT        id AS ancestor,        id AS descendant,        0  AS depth    FROM dbo.category    UNION ALL    SELECT        CTE.ancestor  AS ancestor,        C.id          AS descendant,        CTE.depth + 1 AS depth    FROM dbo.category AS C    JOIN category_cte AS CTE        ON C.parent_id = CTE.descendant)SELECT * FROM category_cte

Result

ancestor descendant depth-------- ---------- -----1        1          0     <- anchor query2        2          03        3          04        4          02        4          1     <- first recursive query1        2          11        3          11        4          2     <- second recursive query

I think I've been able to work out the solution myself.

If anyone has a better way of doing this, please comment.

IF OBJECT_ID('dbo.ClosureTable', 'U') IS NOT NULL    DROP TABLE dbo.ClosureTableGOCREATE TABLE dbo.ClosureTable (    ancestor int NOT NULL,    descendant int NOT NULL,    distance int NULL)GODECLARE @depth INTSET @depth = 1INSERT INTO dbo.ClosureTable (ancestor, descendant, distance)SELECT catid, catid, 0 FROM dbo.Category -- insert all the self-referencing nodesWHILE (@depth < 4) -- my tree is only 4 levels deep, i.e 0 - 3BEGIN    INSERT INTO dbo.ClosureTable (ancestor, descendant, distance)    SELECT ct.ancestor, h.catid, @depth    FROM dbo.ClosureTable ct INNER JOIN dbo.CategoryHierarchy h ON ct.descendant = h.parentid    WHERE ct.distance = @depth - 1    SET @depth = @depth + 1END

Cheers :)

  WITH Children (id, name, iteration) AS (    SELECT id, name, 0    FROM Category     --WHERE id = @folderid -- if you want a startpoint    UNION ALL     SELECT b.id, b.name, a.iteration + 1    FROM Children AS a, Category AS b, CategoryHierarchy c    WHERE a.id = c.parentId      AND b.id = c.catId   )  SELECT id, name, iteration FROM Children

Not tested, but should work like this. At least a start how you do this fast without loops.

EDIT: I missred, not relId, it is parentId that has to link to Childrens table. But the result should be a table with all tables. So this is not what your originally wanted?