ID (int)
ParentID (int)
Name varchar(100)
SeqOrder (int)

With Tree As
(
       Select ID, ParentID, Name, SeqOrder, 0 LevelNumber From dbo.MyTables1 Where ParentID Is Null
       Union All
       Select L.ID, L.ParentID, L.Name, L.SeqOrder, LevelNumber + 1 From dbo.MyTables1 L Join Tree T On T.ID = L.ParentID
)
Select Space(LevelNumber * 5) + Cast(SeqOrder as varchar(10)) + ' ' + Name From Tree

1 Фрукты
    1 Яблоко
    2 Апельсин
2 Овощи
    1 Картофель
    2 Лук
...

With Tree As
(
       Select ID, ParentID, Name, SeqOrder, 0 LevelNumber, ord = cast(SeqOrder as binary(4)) From dbo.MyTables1 Where ParentID Is Null
       Union All
       Select L.ID, L.ParentID, L.Name, L.SeqOrder, LevelNumber + 1,  ord = t.ord + cast(l.SeqOrder as binary(4)) From dbo.MyTables1 L Join Tree T On T.ID = L.ParentID
)
Select Space(LevelNumber * 5) + Cast(SeqOrder as varchar(10)) + ' ' + Name From Tree order by ord 

,  ord = cast(cast(SeqOrder as binary(4)) as varbinary(8000))