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-28T10:45:00.800

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 '22 at 10:45

answered Sep 28 '22 at 08:49

Adrian White

1,720
12
14

https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html – Adrian White Sep 28 '22 at 08:49
1

But there are no levels generating here like example. Only the Path – Ajax Sep 28 '22 at 09:08
https://docs.snowflake.com/en/sql-reference/constructs/pivot.html – Adrian White Sep 28 '22 at 10:45
Can you elaborate with complete code? – Ajax Sep 28 '22 at 11:14
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 – Ajax Sep 28 '22 at 18:30
Extend the "... index in (2,3,4) ..." to "... index in (2,3,4,...78) ..." . Then extend the "... answer(path,nodename1, ... nodename78) ...." – Adrian White Sep 28 '22 at 19:04
But i know the levels as i used the data in Qlik and there it has hierarchy function which automatically generates the maximun Level. So, i know that Levels which will be created are till Level 11. I modified the query a bit. "Start with "Level Parent" = "Level Parent" connect by "Level Parent" = Prior "Level". It exploded the rows more than 50k. But missing the Level1 value in Path /1223/343 – Ajax Sep 28 '22 at 19:25
I wrote an article explaining how to pivot more than 1 item. This could be done - however I'd need more sample data. https://medium.com/@adrian.white/pivot-multiple-values-in-sql-with-snowflake-5d570636a352 – Adrian White Sep 28 '22 at 20:07
Thanks for the article! i will go through it, but i think the root of the problem is that in data there are multiple Parent whose don't have child. In above data there is only Europe, but there could be like Asia and its hiererachy. – Ajax Sep 28 '22 at 20:12

score 1 · Answer 2 · answered Sep 28 '22 at 14:55

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

Getting different Hierarchy levels and Path in Snowflake SQL

2 Answers2