Generating hierarchy table with levels representing the Parent, child, hierarchy levels and Path

Occasional Visitor

So far i am using this query but this query is not able to generate correct rows if levels are increased in the hierarchy.

 CREATE OR REPLACE TABLE Hierarchy
("NodeID" varchar, "ParentID" varchar,"NodeName" varchar);

 Insert into "SCRIPT_TEST"."PUBLIC"."HIERARCHY"
("NodeID", "ParentID","NodeName")

VALUES
('1','4','London'),

('2','3','Munich'),

('3','5','Germany'),

('4','5','UK'),

('5','', 'Europe');

--Query1:

with recursive CteTree as

(Select "NodeID","ParentID","NodeName" as "NodeName1",

         CAST (NULL AS varchar(255)) as "NodeName2",

         CAST (NULL AS varchar(255)) as "NodeName3",

         0 as NodeName

         from "SCRIPT_TEST"."PUBLIC"."HIERARCHY" Where "ParentID" is not null



         Select child."NodeID", child."ParentID", "NodeName1",

         Case When NodeName+1 = 1 then "NodeName" else "NodeName2" end,

         Case When NodeName+1 = 2 then "NodeName" else "NodeName3" end,

         NodeName+1

         from CteTree



         UNION all



         Select "NodeID", "ParentID", "NodeName" as "NodeName1",

         Case When NodeName1+1 = 1 then "NodeName1" else "NodeName2" end,

         Case When NodeName1+1 = 2 then "NodeName1" else "NodeName3" end,

         NodeName+1

         from "SCRIPT_TEST"."PUBLIC"."HIERARCHY" )
        select distinct * from CteTree order by "NodeName1"

         Join "SCRIPT_TEST"."PUBLIC"."HIERARCHY" child

         ON child."ParentID" = CteTree."NodeID"

)

select distinct * from CteTree order by "NodeName1"

)

select distinct * from CteTree order by "NodeName1"

 

"In this query the values of level2 are also coming in level1 "

 

 

Query2:

with Hier (NodeID, ParentID,NodeName) as 
(select * from VALUES
('1','4','London'),
('2','3','Munich'),
('3','5','Germany'),
('4','5','UK'),
('5','',  'Europe'))   

select * from (select nodeid,parentid,path,index, max(value) 
over (partition by nodeid, index) CT from

(select sys_connect_by_path(NodeName, '\\')
 path,nodeid,parentid from Hier 
START WITH ParentID='' connect by PRIOR NodeID = ParentID),
table(split_to_table(path, '\\')))

 pivot(max(CT) for index in (2,3,4)) as answer (nodeid,parentid,path,nodename1,nodename2,nodename3)

"Query 2 is working but unable to produce result if levels are increased"

 

How this can be achieved? Creating the hierarchy tables with levels and Path. (Level are known for the Hierarchy)

0 Replies