create table #Test 
        (
            ID int not null,
            Parent_ID int not null,
            [Name] varchar(15) not null,
            Coast money null
        ); 
              
insert into #Test values(1, 0, 'Продукт', null);
insert into #Test values(2, 1, 'Молочный', null);
insert into #Test values(3, 1, 'Рыба', null);
insert into #Test values(4, 2, 'Молоко', 30.00);
insert into #Test values(5, 2, 'Кефир', 60.00);
insert into #Test values(6, 3, 'Треска', 400.00);
insert into #Test values(7, 3, 'Семга', 700.00);

with cteRN
     as (
         select *,
                row_number() over(partition by Parent_ID order by([Name])) as rn
         from   #Test
     ),
     ctePath
     as (
         select ID,
                Parent_ID,
                [Name],
                0 as lvl,
                convert(varchar(max), id) as sortpath
         from   #Test
         where  ID = 1
         
         union all
         
         select C.ID,
                C.Parent_ID,
                C.[NAME],
                P.lvl + 1,
                convert(
                    varchar(max),
                    P.sortpath + '.' + convert(varchar(max), c.rn)
                )           as sortpath           
         from   ctePath     as P
                join cteRN  as C on C.Parent_ID = P.ID
     )

select ID,
       Parent_ID,
       replicate(' | ', lvl) + [NAME] as ProductTree
from   ctePath
order by sortpath;

drop table #Test;

ID	Parent_ID	ProductTree	Coast
1	0	        Продукт		1180,00
2	1	 	| Молочный	80,00
5	2	 	|  | Кефир	60,00
4	2	 	|  | Молоко	30,00
3	1	 	| Рыба		1100,00
7	3	 	|  | Семга	700,00
6	3	 	|  | Треска	400,00