So I have a table that looks like this:
And here's my query for that table:
select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price
What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.
Locationcode 001 is for selling and 002 is for stock quantity.
I want to make it look like this:
Barcode ItemCode Color Size Class LocCode1 LocCode2 Price Sell Qty Stock Qty
888200000001 W09118-DP28U N/A 29 A 001 002 200 1 1
I'm kinda clueless with merging stuffs like this, so thanks in advance!
Best Answer
I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.