How to convert flattened hierarchy to Parent child hierarchy?

Question

Hi All,
I have a flattened hierarchy table like this and I want to convert this to Parent child hierarchy table to use into TM1. Benefit of converting from flattened hierarchy to Parent child hierarchy table is that if tomorrow we add few more new Hierarchy Levels, no change required on TM1.

Would like to know T-SQL query to convert this:

flattened hierarchy table: Here Account_HL1 is top Level and Account is bottom level in Hierarchy.

and I am looking for outcome like Parent child hierarchy table:

I found this https://stackoverflow.com/questions/16879568/how-can-i-convert-the-following-flat-records-to-a-hierarchical-structure-in-sql
but here it is done using Union and would like to know are there any other ways so that this can be done dynamically considering there can be more levels added in future.

Answer 1

Hi @Shivendoo Kumar ，
Please refer to:

declare @test table(col1 char(25),col2 char(25),col3 char(25),col4 char(25),amount int)  
    insert into @test values('storeprofit','grossprofit','netsales','sales',2222),  
                            ('storeprofit','grossprofit','purchases','purchases',2223),  
     ('storeprofit','totaldirectcosts','employeeexpenses','spuer',2224),  
     ('storeprofit','totaldirectcosts','occupancyexpenses','rent&rates',2225),  
     ('storeprofit','totaldirectcosts','advertisingexpenses','advertisingexpenses',2226)  
select null parent,max(col1) child,1 as level from @test  
union all  
select distinct col1 parent, col2 child,2 as level from @test  
union all  
select distinct col2 parent, col3 child,3 as level from @test  
union all  
select distinct col3 parent, col4 child,4 as level from @test  
union all  
select distinct col4 parent, cast(amount as char(15))child,5 as level from @test   

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.