title: SQL How to create output with sub totals
body: <p>I'm new to T-SQL and need help converting an excel report to a run on SQL. I have a SQL table that records all the daily inventory transactions (in/out) from each stockroom. I need to create a report that list the current inventory levels for each product in each location and the qty in each place as follows. In other words, the current inventory levels of each place. </p>
  
  <p>I also need help on how to insert the Preferred Out Report (below) into SQL Server as a view so I can run this each month over and over again. </p>
  
  <p>Thanks in Advance!</p>
  
  <p><strong>Inventory Log table:</strong></p>
  
  <pre><code>PubID   QTY LocationID  Transaction
  1       10  1           Add
  1       20  2           Add
  1       30  3           Add
  1       5   1           Sold
  1       10  2           Sold
  1       5   3           Sold
  2       10  1           Add
  2       10  2           Add
  2       5   2           Sold
  2       8   2           Sold
  1       20  1           Add
  1       20  2           Add
  2       2   2           Sold
  </code></pre>
  
  <p>Preferred Output Table:</p>
  
  <pre><code>PubID   Local_1 Local_2 Local_3 Total
  1       25      30      25      80
  2       5       0       0       5
  Total   30      30      25      85
  </code></pre>
  
  <p>I see a lot of close examples here but most just add the value while I need to subtract the Sold inventory from the Added stock to get my totals in each column.  </p>
  
  <p>The row totals and column totals on the right and bottom are pluses but not needed if it's easier without. </p>
  
  <p>THANKS!</p>