Use SELECT result as COLUMN name in other SELECT

sql, sql-server, sql-server-2012

Solution

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'

Problem

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' ```

Original source