I have input like with two levels of node as below where Node 1 is a parent node and node 2 is a child. But some child in node 2 like B1, B2, H9 can also have a child and be a parent in node 1.

How to generate the hierarchical structure based on this data via Oracle SQL or Pandas dataframe?

Expected output:

CodePudding user response：

Use

CodePudding user response：

In Oracle, you can use a hierarchical query:

SELECT CASE LEVEL WHEN 2 THEN PRIOR node1 ELSE node1 END AS node0,
       CASE LEVEL WHEN 2 THEN node1 ELSE node2 END AS node1,
       CASE LEVEL WHEN 2 THEN node2 END AS node2
FROM   table_name
WHERE  LEVEL = 2
OR     (LEVEL = 1 AND CONNECT_BY_ISLEAF = 1)
START WITH node1 LIKE 'A%'
CONNECT BY PRIOR node2 = node1

Which, for the sample data:

CREATE TABLE table_name(node1, node2) AS
SELECT 'A1', 'B1' FROM DUAL UNION ALL
SELECT 'A1', 'B2' FROM DUAL UNION ALL
SELECT 'A2', 'B1' FROM DUAL UNION ALL
SELECT 'A2', 'B3' FROM DUAL UNION ALL
SELECT 'B1', 'K5' FROM DUAL UNION ALL
SELECT 'B1', 'I2' FROM DUAL UNION ALL
SELECT 'A3', 'G7' FROM DUAL UNION ALL
SELECT 'A3', 'H9' FROM DUAL UNION ALL
SELECT 'B2', 'J1' FROM DUAL UNION ALL
SELECT 'B2', 'K5' FROM DUAL UNION ALL
SELECT 'H9', 'L7' FROM DUAL;

Outputs:

fiddle