Summarize query table

Question

I have this starting table which is called InventoryItem which shows inventory levels in my warehouse and bin locations:

Inventory ID	Warehouse	Warehouse Location	Qty
11017	Nashville	Dock	10
11017	Nashville	Hold	5
11017	Nashville	A1	13
11017	New York	Hold	20
11119	Chicago	Hold	5
11119	New York	C34	6
11119	New York	Hold	20

I want to end up with the following view which essentially shows what is on hand (any warehouse locations not equal to Hold) and what is on hold (any warehouse locations equal to Hold):

Inventory ID	Warehouse	Qty on Hand(Not Equal to Hold)	Qty on Hold(Equal to hold)
11017	Nashville	23	5
11017	New York	0	20
11119	Chicago	0	5
11119	New York	6	20

Any help would be greatly appreciated.

Answer 1

this will do it:

select [Inventory ID], Warehouse,
    [Qty on Hand] = sum(iif([Warehouse Location] != 'Hold', Qty, 0)),
    [Qty on Hold] = sum(iif([Warehouse Location] = 'Hold', Qty, 0))
from InventoryItem
group by [Inventory ID], Warehouse

..since you're new to SQL Server you might like to know how you could use a bunch of advanced language features to write the same query in a much more complicated way, eg:

with InventoryByHoldState as (
    select i.[Inventory ID], i.Warehouse, s.HoldState, Qty = sum(i.Qty)
    from InventoryItem i cross apply (select
        HoldState = iif(i.[Warehouse Location] = 'Hold',
            'Qty on Hold', 'Qty on Hand')) s
    group by i.[Inventory ID], i.Warehouse, s.HoldState)

select p.* from InventoryByHoldState
pivot (sum(Qty) for HoldState in ([Qty on Hand], [Qty on Hold])) p