I would like to display a table with the following columns:
| SKU | Inventory in WarehouseA | Inventory in WarehouseB |
|---|---|---|
| 123 | 10 | 20 |
Below is my current query:
select a.ItemID, SUM(a.Quantity), b.WarehouseDescription
from dbo.a
INNER JOIN dbo.b on a.WarehouseID = b.WarehouseID
GROUP BY a.ItemID, b.WarehouseDescription
HAVING a.Quantity <>0
and b.WarehouseDescription = 'WarehouseA'
and b.WarehouseDescription = 'WarehouseB'
ORDER BY a.ItemID, b.WarehouseDescription
The output generated so far follows this form:
| SKU | Inventory in WarehouseA | Warehouse A |
| SKU | Inventory in Warehouse B | Warehouse B |
| 123 | 10 | Warehouse A |
| 123 | 20 | Warehouse B |
Feedback/constructive comments are welcome!
CodePudding user response：
Mostly guessing here as we don't have much detail to work with. But something like this is how you might tackle conditional aggregation for this.
select a.ItemID
, WarehouseA = SUM(case when b.WarehouseDescription = 'WarehouseA' then a.Quantity else 0 end)
, WarehouseB = SUM(case when b.WarehouseDescription = 'WarehouseB' then a.Quantity else 0 end)
from dbo.a
INNER JOIN dbo.b on a.WarehouseID = b.WarehouseID
where b.WarehouseDescription in ('WarehouseA', 'WarehouseB')
GROUP BY a.ItemID
HAVING a.Quantity <>0
ORDER BY a.ItemID
, b.WarehouseDescription