Sql-server – Merge two rows into one row

mergesql server

So I have a table that looks like this:

img

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.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,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,c.price