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