1

Topic: Hierarchical structure and total cost.

The table and data:

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, ' the Product ', null);
insert into #Test values (2, 1, ' Dairy ', null);
insert into #Test values (3, 1, ' Fish ', null);
insert into #Test values (4, 2, ' Milk ', 30.00);
insert into #Test values (5, 2, ' Kefir ', 60.00);
insert into #Test values (6, 3, ' the Cod ', 400.00);
insert into #Test values (7, 3, ' the Salmon ', 700.00);

Formation of hierarchical structure:

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 A C on C.Parent_ID = P.ID
)
select ID;
Parent_ID;
replicate (' | ', lvl) + [NAME] as ProductTree
from ctePath
order by sortpath;
drop table #Test;

Help to add request that it was deduced also total cost on groups/subgroups/leaves. Approximately here so:

ID Parent_ID ProductTree Coast
1 0 The Product 1180,00
2 1 | dairy 80,00
5 2 | | kefir 60,00
4 2 | | milk 30,00
3 1 | fish 1100,00
7 3 | | a salmon 700,00
6 3 | | a cod 400,00

2

Re: Hierarchical structure and total cost.

ChambaVamba;

with cteRN
as (
select *;
row_number () over (partition by Parent_ID order by ([Name])) as rn
from #Test
);
ctePath
as (
select ID;
Parent_ID;
--' / ' as Path;
[Name];
0 as lvl;
convert (varchar (max), id) as sortpath;
Coast
from #Test
where ID = 1
union all
select C.ID;
C.Parent_ID;
--p.path+cast (C.ID as varchar (5)) + ' / ';
C. [NAME];
P.lvl + 1;
convert (
varchar (max);
P.sortpath + '. ' + convert (varchar (max), c.rn)
) as sortpath;
C.Coast
from ctePath as P
join cteRN as A C on C.Parent_ID = P.ID
)
select ID;
Parent_ID, - path;
replicate (' | ', lvl) + [NAME] as ProductTree;
(Select sum (b. Coast) From ctePath b Where b.sortpath + '. ' like a.sortpath + '. % ')
from ctePath a
order by sortpath;
drop table #Test;

3

Re: Hierarchical structure and total cost.

Kopelly;
Yes, thanks, work.