Sanjaya Weerakkody Sanjaya Weerakkody - 4 months ago 11
SQL Question

SQL combine columns based on another column

I've got a list of items and two locations (X and Y) that these items can be in.these two locations have these items in different quantities.
So When someone places an order for a few items, the items can be pulled from either of these two locations.

Below is the 'Orders' table I've created but it shows two columns for two locations and available stock.

ItemNumber Location Stock X Stock Y
A X 12 32
B X 10 54
C X 5 23
A Y 54 30
C Y 65 36
D Y 76 23
E X 12 31
F X 32 19
F Y 72 40


What I want to see is available stock for the requested location in a column, not both locations and stock availability in two columns as I've done above.
Result table I Want to see is,

ItemNumber Location Avail Stock
A X 12
B X 10
C X 5
A Y 30
C Y 36
D Y 23
E X 12
F X 32
F Y 40


I just cant get my head around this to do it. great if anyone could help or tell me if its even possible.

Thanks

Answer

You can use a CASE WHEN expression:

SELECT ItemNumber,
       Location, 
       CASE WHEN Location = 'X' THEN [Stock X] 
            WHEN Location = 'Y' THEN [Stock Y] 
       END Avail_Stock 
FROM Orders