sql server – Parent/Child summary rollup

I’m struggling to wrap my head around this one. I’m sure someone will simplify it.

We have a parent/child relationship that I’m needing to roll-up based on sold quantities and sales dollar amounts.

We have a table that stores the parent and their children. Looks like this:

Parent    Component
1000      1300
1000      1301

When an order is placed, our users order a part number of 1000, then add in line items 1300, 1301 as options. Parent parts do not have a sales value assigned to them, but components do have a value. So what I’m trying to do is roll up everything to the parent level (sales and quantities) on a sales order.

Here’s how a sales order would look:

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      0
0001          1300         C              1      500
0001          1301         C              1      350
0001          1301                        1      400

As you can see above, we can have components (StockCode 1301) in here without parents (think of these as replacement parts). If the sales order line does not have a ParentChild value, they need to be excluded from the roll-up.

Here’s how I’m wanting this data to look. A parent/child should only have a Qty of the parent.

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      850
0001          1301                        1      400

I’m guessing that STUFF/FOR XML PATH would be a good starting point?

Here is the test data:

CREATE TABLE #Structure (
  Parent INT
 ,Component INT )
 INSERT INTO #Structure (Parent, Component)
 SELECT 1000, 1300
 SELECT 1000, 1301
 CREATE TABLE #SalesOrder (
   SalesOrder VARCHAR(4)
  ,StockCode INT
  ,ParentChild CHAR(1)
  ,Qty INT
  ,Price INT )

 INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price)
 SELECT '0001', 1000, 'P', 1, 0
 SELECT '0001', 1300, 'C', 1, 500
 SELECT '0001', 1301, 'C', 1, 350
 SELECT '0001', 1301, NULL, 1, 400
 SELECT * FROM #Structure

 SELECT * FROM #SalesOrder

 DROP TABLE #SalesOrder
 DROP TABLE #Structure