Getting different Hierarchy levels and Path in Snowflake SQL

Question

I am trying to replicate the result of a qlik function called Hierarchy. It creates the Hierarchy with different Levels and also gives the Hierarchy Path. The Code that i am using so far is giving me the Levels but with an error that Level2 values are also coming in Level1.

CREATE OR REPLACE TRANSIENT TABLE "Hierarchy" ( "NodeID" VARCHAR, "ParentID" VARCHAR, "NodeName" String)

Insert into "Hierarchy"("NodeID", "ParentID","NodeName")

VALUES
('1','4','London'),
('2','3','Munich'),
('3','5','Germany'),
('4','5','UK'),
('5','',  'Europe');


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 "Hierarchy"
                 Where "ParentID" is not null

                 UNION ALL

                 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

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

)

select distinct * from CteTree order by "NodeName1"

The Output that it is producing:

Desired OutPut:

How can it be achieved?

Adrian White · Accepted Answer · 2022-09-28 10:45:00Z

1

with Hierarchy (NodeID, ParentID,NodeName) as (select * from VALUES
 ('1','4','London'),
 ('2','3','Munich'),
 ('3','5','Germany'),
 ('4','5','UK'),
 ('5','',  'Europe'))   
                                                              
 select   
     sys_connect_by_path(NodeName, ' -> ') path 
    , NodeID
    , ParentID
    , NodeName  
 from 
      Hierarchy 
 START WITH ParentID ='' 
 CONNECT BY PRIOR NodeID =  ParentID ;

edited Sep 28, 2022 at 10:45

answered Sep 28, 2022 at 8:49

Adrian White

1,83413 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Adrian White Over a year ago

docs.snowflake.com/en/sql-reference/constructs/connect-by.html

Ajax Over a year ago

But there are no levels generating here like example. Only the Path

Adrian White Over a year ago

docs.snowflake.com/en/sql-reference/constructs/pivot.html

Ajax Over a year ago

Can you elaborate with complete code?

Ajax Over a year ago

Thanks! Your query works but as the level increases the number of rows are not increasing. Upto level 11 there are less number of rows producing. The Parent table consists of more than 10k rows but output is coming as 78 rows for the hierarchy table

|

Dave Welden · Accepted Answer · 2022-09-28 14:55:16Z

1

CREATE TABLE HIERARCHYWITHLEVEL(NODEID, PARENTID, NODENAME, LEVEL)
AS
WITH TREE AS
(SELECT NODEID, PARENTID, NODENAME, 1 AS LEVEL
 FROM HIERARCHY
 WHERE PARENTID = ''
 UNION ALL
 SELECT HIERARCHY.NODEID, HIERARCHY.PARENTID, HIERARCHY.NODENAME, LEVEL + 1
 FROM HIERARCHY
 JOIN TREE
 ON HIERARCHY.PARENTID = TREE.NODEID
)
SELECT NODEID, PARENTID, NODENAME, LEVEL
FROM TREE;

NODEID	PARENTID	NODENAME	LEVEL
5		Europe	1
3	5	Germany	2
4	5	UK	2
2	3	Munich	3
1	4	London	3

answered Sep 28, 2022 at 14:55

Dave Welden

1,9791 gold badge10 silver badges10 bronze badges

Collectives™ on Stack Overflow

Getting different Hierarchy levels and Path in Snowflake SQL

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related