Sql Snowflake 中的分层数据

嘿，我在Snowflake有一个公司表，定义如下.我已经添加了源表数据和所需目标视图数据的屏幕截图.我还添加了我为该视图编写的代码，但它不能以正确的方式生成结果.

该表名为Companies，视图名为CompanyHierarchyView.我们希望达到的级别是7级.在输出结果中，我只添加了直到级别3以供参考.

由于团队中编码知识的限制，我还需要使用SQL而不是其他语言(如Python或Java)来完成此任务

CREATE OR REPLACE TABLE COMPANIES 

(PARENTCOMPANY VARCHAR(1000),
PARENTID int,
CHILDCOMPANY VARCHAR(1000),
CHILDID int
);

The data in the table is as below:
   Parent Company   Parent ID   Child Company   Child ID
Meta Platforms, Inc.    1   Atlas Solutions, Inc.   2
Meta Platforms, Inc.    1   Acrylic Software    5
Meta Platforms, Inc.    1   American Journalism Project 6
Meta Platforms, Inc.    1   Caffeinated Mind, Inc.  7
Meta Platforms, Inc.    1   Confirm Inc.    8
Meta Platforms, Inc.    1   Digital Staircase Inc.  9
Meta Platforms, Inc.    1   Facebook Technologies, LLC  4
Atlas Solutions, Inc.   2   Accipiter Solutions, LLC    3
Atlas Solutions, Inc.   2   Atlas NetConventions, Inc.  10
Atlas Solutions, Inc.   2   Go Toast, LLC   11
Atlas Solutions, Inc.   2   Ad4ever Inc.    12
Atlas Solutions, Inc.   2   Atleas Search   13
Accipiter Solutions, LLC    3   BidClix, Inc.   14
Facebook Technologies, LLC  4   Jenkins Software LLC    15
Facebook Technologies, LLC  4   Nimble VR   16
Facebook Technologies, LLC  4   13th Lab AB 17
Facebook Technologies, LLC  4   Armature Studios LLC    18
Facebook Technologies, LLC  4   Beat Games s.r.o    19
Facebook Technologies, LLC  4   BigBox VR, Inc. 20
Facebook Technologies, LLC  4   Camouflaj LLC   21
Facebook Technologies, LLC  4   Carbon Design Group, Inc.   22
Facebook Technologies, LLC  4   Downpour Interactive, LLC   23
Facebook Technologies, LLC  4   ImagineOptix Corporation    24
Facebook Technologies, LLC  4   InfiniLED Limited   25


And I would like to create a view which produces results like this:

  Parent Company    Child Company1  Child Company2  Child Company3
Meta Platforms, Inc.    Acrylic Software        
Meta Platforms, Inc.    American Journalism Project     
Meta Platforms, Inc.    Caffeinated Mind, Inc.      
Meta Platforms, Inc.    Confirm Inc.        
Meta Platforms, Inc.    Digital Staircase Inc.      
Meta Platforms, Inc.    Facebook Technologies, LLC  Jenkins Software LLC    
Meta Platforms, Inc.    Facebook Technologies, LLC  Nimble VR   
Meta Platforms, Inc.    Facebook Technologies, LLC  13th Lab AB 
Meta Platforms, Inc.    Facebook Technologies, LLC  Armature Studios LLC    
Meta Platforms, Inc.    Facebook Technologies, LLC  Beat Games s.r.o    
Meta Platforms, Inc.    Facebook Technologies, LLC  BigBox VR, Inc. 
Meta Platforms, Inc.    Facebook Technologies, LLC  Camouflaj LLC   
Meta Platforms, Inc.    Facebook Technologies, LLC  Carbon Design Group, Inc.   
Meta Platforms, Inc.    Facebook Technologies, LLC  Downpour Interactive, LLC   
Meta Platforms, Inc.    Facebook Technologies, LLC  ImagineOptix Corporation    
Meta Platforms, Inc.    Facebook Technologies, LLC  InfiniLED Limited   
Meta Platforms, Inc.    Atlas Solutions, Inc.   Accipiter Solutions, LLC    BidClix, Inc.
Meta Platforms, Inc.    Atlas Solutions, Inc.   Atlas NetConventions, Inc.  
Meta Platforms, Inc.    Atlas Solutions, Inc.   Go Toast, LLC   
Meta Platforms, Inc.    Atlas Solutions, Inc.   Ad4ever Inc.    
Meta Platforms, Inc.    Atlas Solutions, Inc.   Atleas Search   

I have below code written but it is not producing right results. Can someone help:

CREATE OR REPLACE VIEW CompanyHierarchyView AS
WITH RECURSIVE CompanyHierarchy AS (
  SELECT
    PARENTCOMPANY AS ParentCompany,
    PARENTID,
    CHILDCOMPANY AS ChildCompany,
    CHILDID,
    ROW_NUMBER() OVER (PARTITION BY PARENTCOMPANY, PARENTID ORDER BY CHILDID) AS ChildNum
  FROM COMPANIES -- Replace with your table name
  
  UNION ALL
  
  SELECT
    t.PARENTCOMPANY AS ParentCompany,
    t.PARENTID,
    t.CHILDCOMPANY AS ChildCompany,
    t.CHILDID,
    c.ChildNum
  FROM COMPANIES t
  INNER JOIN CompanyHierarchy c ON t.PARENTID = c.CHILDID
)
SELECT
  ParentCompany,
  MAX(CASE WHEN ChildNum = 1 THEN ChildCompany ELSE NULL END) AS "Child Company1",
  MAX(CASE WHEN ChildNum = 2 THEN ChildCompany ELSE NULL END) AS "Child Company2",
  MAX(CASE WHEN ChildNum = 3 THEN ChildCompany ELSE NULL END) AS "Child Company3",
  MAX(CASE WHEN ChildNum = 4 THEN ChildCompany ELSE NULL END) AS "Child Company4",
  MAX(CASE WHEN ChildNum = 5 THEN ChildCompany ELSE NULL END) AS "Child Company5",
  MAX(CASE WHEN ChildNum = 6 THEN ChildCompany ELSE NULL END) AS "Child Company6",
  MAX(CASE WHEN ChildNum = 7 THEN ChildCompany ELSE NULL END) AS "Child Company7"
FROM CompanyHierarchy
GROUP BY ParentCompany, PARENTID
ORDER BY 1, 2, 7;

会很感激这里的任何帮助.