SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.GraphNode 
(
    item character(1) NOT NULL PRIMARY KEY, 
        CONSTRAINT uq_node UNIQUE ($node_id)
) AS NODE;

CREATE TABLE dbo.GraphEdge 
(
    CONSTRAINT GraphEdge_GraphNode 
        CONNECTION (dbo.GraphNode TO dbo.GraphNode) 
        ON DELETE CASCADE,

    CONSTRAINT uq_edge 
        UNIQUE ($from_id, $to_id),
) AS EDGE;
INSERT dbo.GraphNode 
    (item)
VALUES 
    ('a'), ('b'), ('c'), 
    ('d'), ('e'), ('f'),
    ('g'), ('h');
8 rows affected

INSERT dbo.GraphEdge
    ($from_id, $to_id)
SELECT
    F.$node_id, T.$node_id
FROM 
(
    VALUES
        ('a', 'b'),
        ('b', 'c'),
        ('b', 'd'),
        ('e', 'f'),
        ('f', 'g'),
        ('g', 'h')
) AS V (from_item, to_item)
JOIN dbo.GraphNode AS F 
    ON F.item = V.from_item
JOIN dbo.GraphNode AS T 
    ON T.item = V.to_item;
    
6 rows affected

INSERT dbo.GraphEdge
    ($from_id, $to_id)
SELECT
    E.$to_id, E.$from_id 
FROM 
    dbo.GraphEdge AS E;
6 rows affected

--INSERT dbo.GraphEdge
--    ($from_id, $to_id)
--SELECT
--    N.$node_id, N.$node_id
--FROM 
--    dbo.GraphNode AS N ;
CREATE TABLE #TreeItems
(
    tree_id bigint NOT NULL, 
    item char(1) NOT NULL UNIQUE, 
    
    PRIMARY KEY (tree_id, item)
);

CREATE TABLE #Trees
(
    tree_id bigint NOT NULL PRIMARY KEY, 
    node_list varchar(8000) NOT NULL
);
-- Find trees
WITH R AS
(
    -- Tree anchor: Nodes with no parent
    SELECT GN.item, tree_id = ROW_NUMBER() OVER (ORDER BY GN.item)
    FROM dbo.GraphNode AS GN
    WHERE NOT EXISTS 
        (SELECT * FROM dbo.GraphEdge AS GE WHERE GE.$to_id = GN.$node_id)

    UNION ALL

    -- Recursive: Connected nodes
    SELECT GN2.item, R.tree_id
    FROM R
    JOIN dbo.GraphNode AS GN1 ON GN1.item = R.item
    JOIN dbo.GraphEdge AS GE ON GE.$from_id = GN1.$node_id
    JOIN dbo.GraphNode AS GN2 ON GN2.$node_id = GE.$to_id
)
INSERT #TreeItems
(
    tree_id, 
    item
)
SELECT
    R.tree_id,
    R.item
FROM R;

-- Cache trees
INSERT #Trees
(
    tree_id,
    node_list
)
SELECT TI.tree_id, STRING_AGG(TI.item, '->') WITHIN GROUP (ORDER BY TI.item)
FROM #TreeItems AS TI
GROUP BY TI.tree_id
-- Nodes and trees they're in
SELECT TI.item, T.node_list
FROM #TreeItems AS TI
JOIN #Trees AS T ON T.tree_id = TI.tree_id
ORDER BY TI.item;


item
node_list


SELECT item,
       STRING_AGG(last_node, '->') WITHIN GROUP (ORDER BY last_node) AS closure
FROM
(
SELECT gn.item,
       --COUNT(*)
       --gn2.item --,
       LAST_VALUE(gn2.item) WITHIN GROUP (GRAPH PATH) AS last_node
FROM
    dbo.GraphNode AS gn,
    dbo.GraphEdge FOR PATH AS ge, -- WHERE GE.$to_id = GN.$node_id
    dbo.GraphNode FOR PATH AS gn2 
WHERE MATCH(SHORTEST_PATH(gn(-(ge)->gn2)+))
) AS t
GROUP BY item;


item
closure


a
a->b->c->d

b
a->b->c->d

c
a->b->c->d

d
a->b->c->d

e
e->f->g->h

f
e->f->g->h

g
e->f->g->h

h
e->f->g->h
item	closure
a	a->b->c->d
b	a->b->c->d
c	a->b->c->d
d	a->b->c->d
e	e->f->g->h
f	e->f->g->h
g	e->f->g->h
h	e->f->g->h