Use SELECT result as COLUMN name in other SELECT

Question

Is it possible to use the result of a select as a string to concatenate with another string in column name in other select?

Example:

SELECT brand 
FROM articles a 
WHERE a.id='12345678'

Result: BRAND_A

I now want to concatenate _PRICE to BRAND_A...

SELECT (
        SELECT brand
        FROM articles a
        WHERE a.id = '12345678'
        ) + "_PRICE"
FROM prices p
WHERE p.id = '12345678'

...to actually retrieve:

SELECT BRAND_A_PRICE
FROM prices p
WHERE p.id = '12345678'

Answer 1

You don't need dynamic SQL to do this (and dynamic SQL should be avoided if at all possible). Instead you can use a CASE statement. You can do this with a single statement but I've split it out for display purposes:

DECLARE @brand VARCHAR(100) = (SELECT brand FROM articles a WHERE a.id='12345678')

SELECT CASE @brand
           WHEN 'BRAND_A' THEN BRAND_A_PRICE
           WHEN 'BRAND_B' THEN BRAND_B_PRICE
           WHEN 'BRAND_C' THEN BRAND_C_PRICE
           ELSE 0 END AS PRICE
FROM prices
WHERE id='12345678'

Answer 2

I think you need a Dynamic SQL query.

First store the Brand for the particular ID in a variable then use that variable in Dynamic Query appended with the _PRICE to get the result.

DECLARE @sql   NVARCHAR(max),
        @brand VARCHAR(500)

DECLARE @sql   NVARCHAR(max),
        @brand VARCHAR(500)

SELECT @brand = brand
FROM   articles a
WHERE  a.id = '12345678'

SET @sql ='SELECT ' + @brand + '_PRICE FROM prices p WHERE p.id=''12345678'''

EXEC Sp_executesql @sql