[SOLVED] SQL Server : SELECT Column A from Column B

SQL Server : SELECT Column A from Column B

First, I hope the topic title wasn't too vague, I'm not really sure how to describe this issue.

I have been handed an inventory database that was grown and developed by a few generations of people with varying levels of skill. Now it have tons of gotcha's of things that look alike but aren't quite.

So, while there is a table with cost adjustments for every type of inventory. It is unused instead someone took the time to pre-calculate every possible price and add it to each line of the inventory table:

SELECT Part#, CategoryID, StdCost, ListPrice, Level1, Level2, Level3 
FROM [Inventory] 
WHERE ...

Each row of the [Customers] table has a PriceLevel column that contains one of these values: Level1, Level2, Level3. But now I just found out there is yet another table, for a small subset of customers that get additional discounts. Finding E.DiscountLevel with a Left Join was easy the help I am looking for is with the last column, this other discount table also overrides the customer PriceLevel with a new one for specific categories. I am not sure if it can be done at all or not, but can a cell value from one table be used as a column name in another table of the same SELECT?

Table [Inventory] has columns Level1, Level2, Level3, table [InventoryDiscounts] has a column PriceLevel that contains the Word Level1. Should I just use a second query, or is it possible to somehow SELECT B.(E.Column)

This is the whole query with its many joins. No Bold inside comment blocks, but you can see the Stars around the trouble spot.

SELECT 
    A.InventoryID, B.Description, D.Category, 
    B.ListPrice, B.Level3 AS Price,
    E.DiscountLevel, **B.(E.PriceLevel) AS AltPrice**
FROM 
    [BranchInventory] A 
INNER JOIN 
    [Inventory] B ON A.InventoryID = B.InventoryID 
INNER JOIN 
    [Branches] C ON A.BranchID = C.BranchID  
INNER JOIN 
    [Categories] D ON B.CategoryID = D.CategoryID 
LEFT JOIN 
    [InventoryDiscounts] E ON E.CustomerID = 32 AND B.CategoryID = E.CategoryID
WHERE 
    A.BranchID = 8 
    AND A.InventoryID = 1181691

I expect the answer is going to be 'Just use an extra query', and that is ok. But frequently you guys are able to work some crazy magic, and I would love it if that was the case.

Solution

You can easily do this with CASE:

SELECT A.InventoryID
    ,B.Description
    ,D.Category
    ,B.ListPrice
    ,B.Level3 AS Price
    ,E.DiscountLevel
    ,CASE 
        WHEN E.PriceLevel = 'Level1'
            THEN B.Level1
        WHEN E.PriceLevel = 'Level2'
            THEN B.Level2
        WHEN E.PriceLevel = 'Level3'
            THEN B.Level3
        END AS AltPrice
FROM [BranchInventory] A
INNER JOIN [Inventory] B ON A.InventoryID = B.InventoryID
INNER JOIN [Branches] C ON A.BranchID = C.BranchID
INNER JOIN [Categories] D ON B.CategoryID = D.CategoryID
LEFT JOIN [InventoryDiscounts] E ON E.CustomerID = 32
    AND B.CategoryID = E.CategoryID
WHERE A.BranchID = 8
    AND A.InventoryID = 1181691