How to Create Query from Parent Child Hierarchy Table

How to create query from parent child hierarchy table

If you have a fixed or limited number of levels, you may not need DYNAMIC SQL. "Parsing" the path can be accomplished with a little XML.

Consider the following:

Example:

Declare @YourTable Table ([Parent] varchar(50),[Child] varchar(50))
Insert Into @YourTable Values 
 (null ,'S-1')
,('S-1','S-11')
,('S-1','S-12')
,('S-1','S-13')
,('S-1','S-14')
,('S-1','S-15')
,('S-11','S-111')
,('S-11','S-112')

;with cteP as (
      Select Child
            ,Parent 
            ,PathID = cast(Child as varchar(500))
      From   @YourTable
      Where  Parent is Null
      Union  All
      Select Child  = r.Child
            ,Parent = r.Parent 
            ,PathID = cast(p.PathID+','+cast(r.Child as varchar(25)) as varchar(500))
      From   @YourTable r
      Join   cteP p on r.Parent  = p.Child)
Select [Group] = Child
      ,B.*
 From  cteP A
 Cross Apply (
                Select Level1 = xDim.value('/x[1]','varchar(max)')
                      ,Level2 = xDim.value('/x[2]','varchar(max)')
                      ,Level3 = xDim.value('/x[3]','varchar(max)')
                      ,Level4 = xDim.value('/x[4]','varchar(max)')
                      ,Level5 = xDim.value('/x[5]','varchar(max)')
                From  (Select Cast('<x>' + replace(PathID,',','</x><x>')+'</x>' as xml) as xDim) as X 
             ) B
  Order By PathID

Returns

Need help to construct the query from parent-child structure table

Here is an recursive CTE for your query. I haven't finished the formatting but it gives you the recursive CTE.

You haven't specified your RBMS, this is in SQL server. See dbFiddle link at the bottom.

create table nodes(
Rec int,
Node varchar(5),
Parent_Node varchar(5));

insert into nodes values
(1,'A',null),
(2,'B',null),
(3,'C1','A'),
(4,'EE','A'),
(5,'EF','B'),
(6,'G1','B'),
(7,'HH','C1'),
(8,'IU','C1'),
(9,'JK','EF'),
(10,'JA','G1'),
(11,'JJ','G1'),
(12,'OT','HH'),
(13,'PT','JA');

with noder as 
( 
select  
  Parent_Node n1, 
  Node n2, 
  cast(null as varchar(5)) n3, 
  cast(null as varchar(5)) n4, 
  cast(null as varchar(5)) n5
from nodes
  union all 
select 
  Parent_node,n1,n2,n3,n4
from nodes
join noder on n1 = Node 
)
select *
from Noder 
where n1 is not null

n1 | n2 | n3   | n4   | n5  
:- | :- | :--- | :--- | :---
A  | C1 | null | null | null
A  | EE | null | null | null
B  | EF | null | null | null
B  | G1 | null | null | null
C1 | HH | null | null | null
C1 | IU | null | null | null
EF | JK | null | null | null
G1 | JA | null | null | null
G1 | JJ | null | null | null
HH | OT | null | null | null
JA | PT | null | null | null
G1 | JA | PT   | null | null
B  | G1 | JA   | PT   | null
C1 | HH | OT   | null | null
A  | C1 | HH   | OT   | null
B  | G1 | JJ   | null | null
B  | G1 | JA   | null | null
B  | EF | JK   | null | null
A  | C1 | IU   | null | null
A  | C1 | HH   | null | null

db<>fiddle here

Translate a table with parent / child hierarchy into a table with parent name in separate columns?

You can use a standard recursive CTE to build the hierarchy and desired path of Names. Then it is a small matter to parse || delimited path with a bit of JSON.

;with cteP as (
      Select Name
            ,Code
            ,Parent 
            ,PathID = string_escape(Name,'JSON')
      From   YourTable
      Where  Parent is Null
      Union  All
      Select r.Name
            ,Child  = r.Code
            ,Parent = r.Parent 
            ,PathID = p.PathID+'||'+string_escape(r.Name,'JSON')
      From   YourTable r
      Join   cteP p on r.Parent  = p.Code)
Select A.Name
      ,A.Code
      ,A.Parent
      ,Country  = JSON_VALUE(S,'$[0]')
      ,State    = JSON_VALUE(S,'$[1]')
      ,District = JSON_VALUE(S,'$[2]')
      ,Taluk    = JSON_VALUE(S,'$[3]')
 From  cteP A
 Cross Apply ( values ( '["'+replace(PathID,'||','","')+'"]' ) ) B(S)

Results

Generate a parent-child hierarchy from table with levels paths

You could create a new table with the hierarchical structure, and an auto incrementing ID, like this:

create table hierarchy (
  id int not null identity (1,1) primary key,
  element varchar(100),
  parent int
);

Then you would first add the level 1 elements to it, as they have no parent:

insert into hierarchy (element, parent)
  select     distinct f.level1, null
  from       flat f;

As you now have the id values generated for these elements, you can add the next level, like this:

insert into hierarchy (element, parent)
  select     distinct f.level2, h1.id
  from       hierarchy h1
  inner join flat f
          on f.level1 = h1.element
  where      h1.parent is null;

This pattern you can repeat to the next levels:

insert into hierarchy (element, parent)
  select     distinct f.level3, h2.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level4, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level5, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
  where      h1.parent is null;

insert into hierarchy (element, parent)
  select     distinct f.level6, h3.id
  from       hierarchy h1
  inner join hierarchy h2
          on h2.parent = h1.id
  inner join hierarchy h3
          on h3.parent = h2.id
  inner join hierarchy h4
          on h4.parent = h3.id
  inner join hierarchy h5
          on h5.parent = h4.id
  inner join flat f
          on f.level1 = h1.element
         and f.level2 = h2.element
         and f.level3 = h3.element
         and f.level4 = h4.element
         and f.level5 = h5.element
  where      h1.parent is null;

... etc, as far into the levels as needed.

Root parent for a parent child hierarchy

If I understand correctly, you can just tweak the CTE to fetch whatever list you want:

with cte as (
       select company_key, company_name, company_key as parent_key, 0 as lev
       from company
       where company_key in (103, 107)  -- whatever list you want here
       union all
       select c.company_key, c.company_name, cte.parent_key, 1 + cte.lev
       from cte join
            company c
            on cte.company_key = c.parent_key
      )
select * 
from cte
order by parent_key, lev;

Here is a db<>fiddle.

How can the complete hierarchy of the parent child relation ship be shown in big query

Ok So after much struggle of searching the internet and trying out multiple options here is what I have come up with , it took a lot of time to understand the details but I think I have found a solution. Maybe it will save people of the trouble that I went though. I will try to explain as I go

-- Initialise variables 
DECLARE steps INT64 DEFAULT 1;
DECLARE table_holder ARRAY<STRUCT<original_child INT64, latest_parent INT64,path STRING>>;

--- Set up dummy tables 

CREATE TEMP TABLE rell AS 
  SELECT 3 child_id, 2 parent_id UNION ALL
  SELECT 2, 1 UNION ALL
  SELECT 4, 1 UNION ALL
  SELECT 6, 2 UNION ALL
  SELECT 14, 6 UNION ALL
    SELECT 15, 14 UNION ALL
  SELECT 7, 8 UNION ALL
  SELECT 8, 5 UNION ALL
  SELECT 9, 10 UNION ALL
  SELECT 11, 12 ;

CREATE TEMP TABLE mapp AS 
  SELECT 1 item_id, 'app' type UNION ALL
  SELECT 2 , 'ci'  UNION ALL
  SELECT 3 , 'ci'  UNION ALL
  SELECT 4 , 'ci'  UNION ALL
  SELECT 5 , 'app'  UNION ALL
  SELECT 6 , 'ci'  UNION ALL
  SELECT 7 , 'ci'  UNION ALL
  SELECT 8 , 'ci'  UNION ALL
  SELECT 9 , 'app'  UNION ALL
  SELECT 10 , 'ci'  UNION ALL
  SELECT 11 , 'ci'  UNION ALL
  SELECT 14 , 'ci'  UNION ALL
  SELECT 15 , 'ci'  UNION ALL
  SELECT 12 , 'ci' ;

  SET table_holder = (
            SELECT ARRAY_AGG(STRUCT(a.item_id,
            b.parent_id,    CONCAT(CAST(a.item_id AS STRING),">",CAST(b.parent_id AS STRING)))
            ) cls from mapp a inner join rell b  on a.item_id = b.child_id where a.type!='app') ;

    LOOP 
   SET table_holder = (
   SELECT ARRAY_AGG(STRUCT(a.original_child,
      coalesce(b.parent_id,a.latest_parent),   coalesce( CONCAT(path,">",CAST(b.parent_id AS STRING)),path))
      ) cls from UNNEST (table_holder) a left outer join rell b  on a.latest_parent = b.child_id ) ;  

  SET steps = steps+1;
  IF steps=5 THEN LEAVE; END IF;

    END LOOP;

   SELECT * from UNNEST (table_holder);

Arrays and struct have been utilised as they are easier to play with. and bigquery scripting has been used for looping. Runaway condition can be increased if people expect many levels.

Here is the final output

original_child  final_parent    path
4   1   4>1
3   1   3>2>1
7   5   7>8>5
14  1   14>6>2>1
15  1   15>14>6>2>1
11  12  11>12
2   1   2>1
8   5   8>5
6   1   6>2>1

Hope it helps someone down the line for similar exercise.

---

Related Topics