https://stackoverflow.com/questions/59291985/hierarchical-tree-table-sql-query-from-one-table

tree.sql

-- src table
create table #src(
  partno      varchar(10),
  partno_item varchar(10)
)

insert #src(partno, partno_item) values('123-01', '1234-01')
insert #src(partno, partno_item) values('123-01', '1234-02')
insert #src(partno, partno_item) values('123-01', '1234-03')
insert #src(partno, partno_item) values('1234-03', '1234-04')
insert #src(partno, partno_item) values('1234-04', '1234-05')
insert #src(partno, partno_item) values('123-02', '1234-06')

select * from #src
go


-- get items
create procedure getItems
  @partno     varchar(10)
as
  declare
    @lvl      int

  select @lvl = 1

  -- tmp table
  create table #tmp(
    partno_item varchar(10),
    lvl         int
  )

  if exists(
    select partno from #src where (partno = @partno)
  ) begin
    insert #tmp(partno_item, lvl)
    select distinct partno_item, @lvl from #src where (partno = @partno)

    while exists(
      select distinct s.partno_item
      from #src s, #tmp t
      where (t.lvl = @lvl)
        and (t.partno_item = s.partno)
    ) begin
      select @lvl = @lvl + 1    -- increase lvl

      insert #tmp(partno_item, lvl)
      select distinct s.partno_item, @lvl
      from #src s, #tmp t
      where (t.lvl = @lvl - 1)
        and (t.partno_item = s.partno)
    end
  end

  select * from #tmp
  truncate table #tmp
  drop table #tmp
go


-- exec
exec getItems '123-01'
go


truncate table #src
drop table #src
drop procedure getItems
go