0

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.