How to Select all possible distinct related "two-ways" values for any given ID from a junc-CodePudding

My question is how to add the values I-95 as it related to I-300 and I-390 as it relate to I-20 to IO_RELATED output

The sample data is:

CREATE TABLE TBL_IORELATE (ID, MainID, RelatedID) AS
SELECT 1, 'I-225', 'I-20' FROM DUAL UNION ALL
SELECT 2, 'I-225', 'I-35' FROM DUAL UNION ALL
SELECT 3, 'I-225', 'I-300' FROM DUAL UNION ALL
SELECT 4, 'I-410', 'I-20' FROM DUAL UNION ALL
SELECT 5, 'I-410', 'I-50' FROM DUAL UNION ALL
SELECT 6, 'I-300', 'I-95' FROM DUAL UNION ALL
SELECT 7, 'I-455', 'I-300' FROM DUAL UNION ALL
SELECT 8, 'I-20', 'I-390' FROM DUAL;

The query I want to adapt from my previous question is:

SELECT id,
       LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
  SELECT DISTINCT id, value
  FROM   (
    SELECT CONNECT_BY_ROOT MainID AS id,
           MainID,
           RelatedID
    FROM   TBL_IORELATE
    START WITH MainID  IN ('I-225')
    CONNECT BY NOCYCLE
       PRIOR MainID = MainID
    OR PRIOR RelatedID = RelatedID
  )
  UNPIVOT(value FOR key IN (MainID, RelatedID))
  WHERE id <> value
)
GROUP BY id

db<>fiddle here

CodePudding user response：

Connect to any mainid to itself or relatedid and vice versa:

SELECT id,
       LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
  SELECT DISTINCT id, value
  FROM   (
    SELECT CONNECT_BY_ROOT MainID AS id,
           MainID,
           RelatedID
    FROM   TBL_IORELATE
    START WITH MainID  IN ('I-225')
    CONNECT BY NOCYCLE
       PRIOR MainID IN (RelatedID, MainID)
    OR PRIOR RelatedID IN (RelatedID, MainID)
  )
  UNPIVOT(value FOR key IN (MainID, RelatedID))
  WHERE id <> value
)
GROUP BY id

Which outputs:

ID IO_RELATED

I-225 I-20,I-300,I-35,I-390,I-410,I-455,I-50,I-95

ID	IO_RELATED
I-225	I-20,I-300,I-35,I-390,I-410,I-455,I-50,I-95

db<>fiddle here