Sql Server Hierarchy One Row To Multiple Column Query

I have a table with structure and record like this ID | Name | ----------------------------------------- 01 | Group Category | 010

Solution 1:

With table structure as shown, and if number of levels is fixed, you can simply self join multiple times using LIKE on ID column as a join condition:

select t1.name, t2.name, t3.name, t4.name
from #t t4
join #t t3 on t4.id like t3.id+'__'join #t t2 on t3.id like t2.id+'__'join #t t1 on t2.id like t1.id+'__'

This should give you the desired output.