Have results split into two colums

[Ken Sheridan]

Which implies there can be 1

That had occurred to me. The following should handle it:

SELECT
    S1.Customer_ID,
    S1.Order_Date,
    S1.Order_No,
    S1.Item_Code,
    MIN(S1.Pick_Bin) AS Pick_Bin_1,
    IIF(
        (
            SELECT
                COUNT(*)
            FROM
                Sales AS S2
            WHERE
                S2.Order_No = S1.Order_No
        ) = 2,
        MAX(S1.Pick_Bin),
        0
    ) AS Pick_Bin_2
FROM
    Sales AS S1
GROUP BY
    S1.Customer_ID,
    S1.Order_Date,
    S1.Order_No,
    S1.Item_Code;

I'd prefer to return zero in Pick_Bin_2 in the event of there being only one item in the order, rather than NULL, due to the semantic ambiguity of the latter.

 

[CJ_London]

easily handled if that is what is required - the OP's example is too simplistic

Iif(max <>min,max,0) as bin2