Entity
---------
ID
TypeAID
TypeBID
TypeAID
and TypeBID
are nullable fields where row can have one, both or none values entered, and both IDs
are pointing to same table Entity.ID
(self reference).
Is there any way to go recursive through 3 level deep structure and get flat structure of parent -> child
, parent -> grandchild
, child -> grandchild
relations?
This is example where first table is normal hierarchy and second is desired flat output.
Data:
Row ID TypeAID TypeBID
1 A NULL NULL
2 B NULL NULL
3 C A NULL
4 D B C
5 E NULL C
Desired result:
Row Parent Child
1 A C
2 A D
3 A E
4 B D
5 C D
6 C E
Using CTE:
DECLARE @t TABLE (Id CHAR(1), TypeAId CHAR(1), TypeBId CHAR(1))
INSERT INTO @t VALUES
('A', NULL, NULL),
('B', NULL, NULL),
('C', 'A', NULL),
('D', 'B', 'C'),
('E', NULL, 'C')
-- All entities flattened
;WITH l1 AS (
SELECT t.TypeAId AS Parent, t.Id AS Child
FROM @t t
WHERE t.TypeAId IS NOT NULL
UNION
SELECT t.TypeBId AS Parent, t.Id AS Child
FROM @t t
WHERE t.TypeBId IS NOT NULL)
-- Join l1 with itself
,l2 AS (
SELECT l1.Parent, l2.Child
FROM l1 l1
INNER JOIN l1 l2 ON l2.Parent = l1.Child)
SELECT * FROM l1
UNION ALL SELECT * FROM l2
ORDER BY Parent