How to find all paths recursively from relational table in SQL Server using transitive property?

Question

I would like to be able to recursively find all relationships in a table. I have a relational table, and essentially I would like to apply the transitive property (i.e. if A~B, B~C => A~C), and with the newly found relationship(s), I would like to recursively do this until there are no more paths to be found. The data is all in SQL-Server. I have found a few other posts and tried to emulate the solutions provided with recursion. Maybe I am just not understanding how to implement my need?

To give some context:

Example contents of the relational table on SQL

A View on SQL that makes the relation multidirectional

What I would like to derive

The relational table is relating the table that the IDs come from to itself, which is why I created the multidirectional view. It made it easier to join on ID, and SELECT ID2.

Consider my table is just called relatedIDs, my view is called relatedView, and the derived view I'd like to make is called derivedView, then pulling from another post I tried something like this:

WITH RECURSIVE derivedView AS
(
  SELECT ID
  FROM relatedView
  UNION ALL
  SELECT r.ID, 
  FROM derivedView d, relatedView r
  WHERE r.ID2 = d.ID
)
SELECT * FROM derivedView;

However I am unable to really even test this as I have "Invalid Syntax near derivedView. Expecting '(', or AS" on the very first line.

As Requested SQL of creating tables and views:

/* RELATIONAL TABLE 
ID  | ID2
---------
213 | 404
404 | 605
*/
CREATE TABLE relatedIDs (
  [ID] [int] NOT NULL,
  [ID2] [int] NOT NULL,
  PRIMARY KEY (ID, ID2)
);
INSERT INTO relatedIDs VALUES (213, 404);
INSERT INTO relatedIDs VALUES (404, 605);


/* MULTIDIRECTIONAL VIEW
ID  | ID2
---------
213 | 404
404 | 213
404 | 605
605 | 404
*/
SELECT relatedIDs.ID, relatedIDs.ID2
FROM relatedIDs
UNION
SELECT relatedIDS.ID2 AS ID, relatedIDs.ID
FROM relatedIDs


/* What I'd Like to Derive: 
ID  | ID2
---------
213 | 404
404 | 213
404 | 605
605 | 404
213 | 605
605 | 213
*/

Answer 1

Please try the following conceptual example.

SQL

-- DDL and sample data population, start
DECLARE @tbl table (
    idGeo INT IDENTITY PRIMARY KEY,
    GEO   VARCHAR(64),
    PARENTID INT
);
INSERT INTO @tbl (GEO, PARENTID) VALUES 
(   'EMEA',    NULL),
(   'France',  1),
(   'Normandy',   2),
(   'Germany', 1),
(   'Gascony',  2),
(   'Americas',    NULL),
(   'US',  6);
-- DDL and sample data population, end

--SELECT * FROM @tbl;

WITH cte AS 
(
    -- Anchor query
    SELECT idGEO, GEO, ParentID, 1 AS [Level]
        , CAST('/' + GEO AS VARCHAR(1000)) AS XPath
    FROM @tbl
    WHERE ParentID IS NULL
    UNION ALL
    -- Recursive query
    SELECT t.idGEO, t.GEO, t.ParentID, cte.[Level] + 1 AS [Level]
        , CAST(cte.[XPath] + '/' +  t.GEO AS VARCHAR(1000)) AS [XPath]
    FROM @tbl AS t
        INNER JOIN cte ON t.ParentID = cte.idGEO
    WHERE t.ParentID IS NOT NULL 
)
SELECT idGEO, GEO
    , REPLICATE('  ',[Level]-1) + GEO AS GEOHierarchy
    , [level]
    , [XPath]
FROM cte
ORDER BY XPath;

Output