'How to find all paths recursively from relational table in SQL Server using transitive property?
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
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
*/
Solution 1:[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;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Yitzhak Khabinsky |