Case logic again!

Transact-SQL
1

I have the following query.
select distinct b.BinName, Si.Code, si.Name, b.ConfirmedQtyInStock+b.UnconfirmedQtyInStock as StockQty, AllocationPriority
, Case When r1.BinName = b.BinName Then r2.BinName Else r1.BinName End as [Location1], Case When r1.BinName = b.BinName Then r2.Qty Else r1.Qty End as [Qty1]

	, Case When r2.BinName In (b.BinName,r1.BinName) Then r3.BinName Else r2.BinName End as [Location2]
	--,r1.BinName, r1.Qty,r2.BinName, r2.Qty,r3.BinName, r3.Qty,r4.BinName, r4.Qty

from BinItem b Inner Join StockItem si On si.ItemID = b.ItemID

  left outer join 
	(select ROW_NUMBER() Over (Partition By si.Code Order By si.Code, ConfirmedQtyInStock+UnconfirmedQtyInStock asc) as ranking
	,si.Code, bi.BinName, bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock as Qty  from stockitem si inner join binitem bi on bi.ItemID = si.itemid
	where bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock >0) r1
	On r1.ranking=1 and r1.Code=si.Code 
	
  left outer join 
	(select ROW_NUMBER() Over (Partition By si.Code Order By si.Code, ConfirmedQtyInStock+UnconfirmedQtyInStock asc) as ranking
	,si.Code, bi.BinName, bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock as Qty  from stockitem si inner join binitem bi on bi.ItemID = si.itemid
	where bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock >0) r2
	On r2.ranking=2 and r2.Code=si.Code
	
  left outer join 
	(select ROW_NUMBER() Over (Partition By si.Code Order By si.Code, ConfirmedQtyInStock+UnconfirmedQtyInStock asc) as ranking
	,si.Code, bi.BinName, bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock as Qty  from stockitem si inner join binitem bi on bi.ItemID = si.itemid
	where bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock >0) r3
	On r3.ranking=3 and r3.Code=si.Code 
	
  left outer join 
	(select ROW_NUMBER() Over (Partition By si.Code Order By si.Code, ConfirmedQtyInStock+UnconfirmedQtyInStock asc) as ranking
	,si.Code, bi.BinName, bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock as Qty  from stockitem si inner join binitem bi on bi.ItemID = si.itemid
	where bi.ConfirmedQtyInStock+bi.UnconfirmedQtyInStock >0) r4
	On r4.ranking=4 and r4.Code=si.Code

Where si.StockItemStatusID=0 and ConfirmedQtyInStock+UnconfirmedQtyInStock >0
and si.code='06403107C'
--and BinName='C01A1'

Order By Code, b.BinName

What I am trying to do is to ensure that only unique locations are in each of the columns. For example:

C03B1 06403107C 1 1/4in COUNTER NUT CHROME PLATED BRASS 543.00000 1 C04A1 9.00000
C04A1 06403107C 1 1/4in COUNTER NUT CHROME PLATED BRASS 9.00000 4 C03B1 543.00000

However I haven't quite worked out the case logic to move all the values in the r1,r2,r3,r4 subqueries correctly.

2

It is difficult to tell what you want without consumable test data.

Try playing around with something like:

WITH AggBin
AS
(
    SELECT B.BinName, S.Code, S.Name
        ,SUM(X.StockQty) AS StockQty
        ,MAX(AllocationPriority) AS AllocationPriority -- What table?
    FROM BinItem B
        CROSS APPLY (VALUES(B.ConfirmedQtyInStock + B.UnconfirmedQtyInStock)) X (StockQty)
        JOIN StockItem S ON B.ItemID = S.ItemID
    WHERE S.StockItemStatusID = 0
        AND X.StockQty > 0
    GROUP BY B.BinName, S.Code, S.Name
)
,QtyOrder
AS
(
    SELECT BinName, Code, Name, StockQty, AllocationPriority
        ,ROW_NUMBER () OVER (PARTITION BY Code ORDER BY StockQty) AS Ranking
    FROM AggBin
)
SELECT Code, Name, AllocationPriority
    ,MAX(CASE WHEN Ranking = 1 THEN BinName ELSE '' END) AS Location1
    ,MAX(CASE WHEN Ranking = 1 THEN StockQty ELSE 0 END) AS StockQty1
    ,MAX(CASE WHEN Ranking = 2 THEN BinName ELSE '' END) AS Location2
    ,MAX(CASE WHEN Ranking = 2 THEN StockQty ELSE 0 END) AS StockQty2
    ,MAX(CASE WHEN Ranking = 3 THEN BinName ELSE '' END) AS Location3
    ,MAX(CASE WHEN Ranking = 3 THEN StockQty ELSE 0 END) AS StockQty3
    ,MAX(CASE WHEN Ranking = 4 THEN BinName ELSE '' END) AS Location4
    ,MAX(CASE WHEN Ranking = 4 THEN StockQty ELSE 0 END) AS StockQty4
FROM QtyOrder
WHERE Code = '06403107C'
GROUP BY Code, Name, AllocationPriority;