SQL Server可变列临时表存储及不确定深度生产订单层级转换问询

现有生产订单数据，父订单与子订单存在供需关系，层级结构如下：

需要将其转换为如下结构，用于关联主父订单组件与子订单，追踪产品组件来源：

无法预测未来订单树状结构的深度，但需创建不会因层级加深而失效的报表。

目前采用手动统计层级深度的方式，示例中主父层级为0时共2层，转换伪代码如下：

Select 
    L.Father_ID as Level0
    , '001' as Hierarchy0
    , L.Child_ID as Level1
    , Dense_Rank() over (partition by L.Father_ID) as Hierarchy1
    , R1.Child_ID as Level2
    , Dense_Rank() over (partition by L.Father_ID, L.Child_ID) as Hierarchy2
Into #exploded_table
From Table as L
Left Join Table as R1
on L.Child_ID  = R1.Father_ID 

Select distinct
    Level0 as Master_Father
    , Concat(Hierarchy0,'.',format(Hirarchy1, '000')) as Hierarchy
    , Level1 as Child
From #exploded_table
Union all
Select distinct
    Level0 as Master_Father
    , Concat(Hierarchy0,'.',format(Hierarchy1, '000'),'.',format(Hierarchy2, '000')) as Hierarchy
    , Level2 as Child
From #exploded_table

该方案存在两大问题：

• 层级越多，代码长度呈线性增长；
• 若未来新增层级，代码会直接失效。

尝试过编写动态代码：先统计最深树的深度，再动态生成对应层级的代码，但SQL Server对非确定性列数的支持不佳。需要在EXEC sp_executesql作用域外创建临时表，再动态修改列以匹配结果，且不能使用全局临时表避免多报表冲突。此方法复杂且可读性差，不符合最佳实践。

此前使用PySpark可轻松实现此类需求，现仅能使用SQL Server和SQL Server Reporting Services（SSRS），特此咨询是否有更简便的实现方式。

内容的提问来源于stack exchange，提问作者Merlin Nestler