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)
