title: Retain First Parent Part in Hierarchy
body: <p>I have a bill of material tree hierarchy as shown below.  I have written a query that results in a flat bill of material showing the total quantity of each component required for the final part taking into consideration the quantity in each relationship.  The issue is I need the result to include the first parent.  See below for sample data, current query and results, and required query results.  Thank you very much for your assistance.</p>
  <p><a href="https://i.stack.imgur.com/Qa1OH.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Qa1OH.jpg" alt="enter image description here" /></a></p>
  <p><a href="https://i.stack.imgur.com/Rpn4y.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Rpn4y.jpg" alt="enter image description here" /></a></p>
  <p>CURRENT QUERY</p>
  <pre><code>with all_item_counts as 
  (
      select 
          ParentId, Id, CAST(Quantity as numeric(38,6)) as q, 0 as level 
      from 
          dbo.MBOM
      union all 
      select 
          all_item_counts. ParentId, dbo.MBOM.Id, Quantity * q, level + 1 
      from 
          all_item_counts
      join 
          dbo.MBOM on dbo.MBOM.ParentId = all_item_counts.Id
  )
  select 
      ParentId, Id, sum(q) 
  from 
      all_item_counts
  where
      ParentId = 1
  group by 
      ParentId, Id
  </code></pre>