sql - parent-child data alignment -

i have stored procedure presents inventory data , standard format , not consider parent-child relationship. current view of data along many other columns:

i'm trying bring parent/child relationship in report. i've modified queries bring in parent/child data in. data needs presented in specific way. pic below:

basically, need list each parent first followed child records. find these records child display on parent row.

the min of child "1st receipt date",  max of child "last receipt date",  sum of child "on hand" sum of child "sales unit" mths supply calculated total on hand , sales units 

i have no clue how arrange data in manner. appreciate direction.

thanks in advance.

-- section stage sample data -------------------------------------------------------------------------------------------------------- declare @inv table (sku int,firstreceiptdate date,lastreceiptdate date,onhand int,salesunits int) insert @inv (sku,firstreceiptdate,lastreceiptdate,onhand,salesunits) values (456,'2014-03-15','2014-12-14',0,15), (789,'2014-05-30','2014-12-15',10,35), (321,'2014-07-31','2016-03-16',112,60)  declare @hier table (sku int,ptsku int,ps int,title varchar(50))  insert @hier (sku,ptsku,ps,title) values (123,0,0,'sku tile 123'), (456,123,10,'some other title sku 456'), (789,123,20,'this title title sku 786'), (321,123,30,'finally tile 321')   -- section builds hierarchy range keys  hierarchies can variable depth -- hierarchies pretty static rebuilt needed -- real power using range key.  can aggregate data without  -- recursive query. -- should not added presentation sequence (ps) , title hierarcy -- ps used control presentation order.  can alphabetical -------------------------------------------------------------------------------------------------------- ;with cteoh (sku,ptsku,lvl,ps,sortseq)       (         select sku,ptsku,lvl=1,ps,cast([dbo].[udf-str-padl](ps,0,6) +':' +[dbo].[udf-str-padl](sku,0,6) + '/' varchar(500)) @hier  ptsku=0         union         select h.sku,h.ptsku,cteoh.lvl+1,h.ps,sortseq=cast(cteoh.sortseq + [dbo].[udf-str-padl](h.ps,0,6) +':' +[dbo].[udf-str-padl](h.sku,0,6) + '/' varchar(500)) @hier h inner join cteoh on  h.ptsku = cteoh.sku      )     ,cter1  (select sku,sortseq,r1=row_number() on (order sortseq) cteoh)     ,cter2  (select a.sku,r2 = max(b.r1) cteoh join cter1 b on (b.sortseq a.sortseq+'%') group a.sku)     select b.r1           ,c.r2           ,a.lvl           ,a.sku           ,a.ptsku           ,a.ps           ,t.title       #tempoh       cteoh      join  cter1 b on (a.sku=b.sku)      join  cter2 c on (a.sku=c.sku)      join  @hier t on (a.sku=t.sku)      order b.r1   -- section illustrates how aggregate data via range keys -------------------------------------------------------------------------------------------------------- select a.*         ,firstreceiptdate       = min(b.firstreceiptdate)       ,lastreceiptdate        = max(b.lastreceiptdate)       ,onhand                 = sum(b.onhand)       ,salesunits             = sum(b.salesunits)       ,monthssupply           = cast(sum(b.onhand*12.)/sum(b.salesunits) money)       ,familyfirstreceiptdate = first_value(min(b.firstreceiptdate)) on (order a.r1)       ,familylastreceiptdate  = first_value(max(b.lastreceiptdate))  on (order a.r1)       ,familyonhand           = first_value(sum(b.onhand))           on (order a.r1)       ,familysalesunits       = first_value(sum(b.salesunits))       on (order a.r1)       ,familymonthssupply     = first_value(cast(sum(b.onhand*12.)/sum(b.salesunits) money))       on (order a.r1)  #tempoh  join (select _r1=b.r1,a.* @inv join #tempoh b on a.sku=b.sku) b on _r1 between a.r1 , a.r2  group a.r1,a.r2,a.lvl,a.sku,a.ptsku,a.ps,a.title  order a.r1 

returns

r1                   r2                   lvl         sku         ptsku       ps          title                                              firstreceiptdate lastreceiptdate onhand      salesunits  monthssupply          familyfirstreceiptdate familylastreceiptdate familyonhand familysalesunits familymonthssupply -------------------- -------------------- ----------- ----------- ----------- ----------- -------------------------------------------------- ---------------- --------------- ----------- ----------- --------------------- ---------------------- --------------------- ------------ ---------------- --------------------- 1                    4                    1           123         0           0           sku tile 123                                       2014-03-15       2016-03-16      122         110         13.3091               2014-03-15             2016-03-16            122          110              13.3091 2                    2                    2           456         123         10          other title sku 456                           2014-03-15       2014-12-14      0           15          0.00                  2014-03-15             2016-03-16            122          110              13.3091 3                    3                    2           789         123         20          title title sku 786                2014-05-30       2014-12-15      10          35          3.4286                2014-03-15             2016-03-16            122          110              13.3091 4                    4                    2           321         123         30          tile 321                                   2014-07-31       2016-03-16      112         60          22.40                 2014-03-15             2016-03-16            122          110              13.3091 

required function

create function [dbo].[udf-str-padl] (@value varchar(50),@pad varchar(10) = '0',@len int = 10)  -- syntax : select [dbo].[udf-str-padl](25,0,10) -- syntax : select [dbo].[udf-str-padl](25,'-',6)  returns varchar(50)   begin     return right(concat(replicate(@pad,@len),@value),@len)   end