The field I want to pull depends on the value of another field. I have a lookup table that looks like this:

I am looking for a way to pull the appropriate value based on the Prefix column. Would be something like this:

Select selected_column = Prefix '_value'

Where the desired result is:

I could easily achieve this with a bunch of CASE/WHEN expressions, but I wanted to see if there's a way to easily call the desired column dynamically.

CodePudding user response：

Well, if it really is A_value -> Z_value, you can use CHOOSE:

SELECT selected_column = CHOOSE
                         (
                           ASCII(Prefix)-64, 
                           A_value, 
                           B_value, 
                           C_value,
                           D_value
                         ) 
FROM dbo.[a lookup table];

• Example db

CodePudding user response：

You could go the JSON route.

select 
cast(json_value(json_values, '$[0].' Prefix '_value') as int) as selected_column
from your_lookup_table t
cross apply (
 select t.* 
 for json path
) ca(json_values)

But if it only a few columns, then maybe using a CASE is the most straightforward method.

select
 case Prefix
 when 'A' then A_value
 when 'B' then B_value
 when 'C' then C_value
 when 'D' then D_value
 end as selected_column
from your_lookup_table

Demo on db<>fiddle here

CodePudding user response：

You can use CURSOR :

declare @t table (Prefix nvarchar(50),A_value int,B_value int,C_value int,D_value int)

insert into @t values ('A', 1,  2,  3,  4)
insert into @t values ('B', 5,  6,  7,  8)
insert into @t values ('C', 9,  10, 11, 12)

declare @result table (selected_column int)
declare @Prefix nvarchar(50),@A_value int,@B_value int,@C_value int,@D_value int

DECLARE db_cursor CURSOR FOR 
SELECT Prefix,A_value,B_value, C_value,D_value
FROM @t 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Prefix,@A_value,@B_value, @C_value,@D_value  

WHILE @@FETCH_STATUS = 0  
 
BEGIN  
    insert into @result
    Select CASE
        WHEN @Prefix='A' THEN @A_value
        WHEN @Prefix='B' THEN @B_value
        WHEN @Prefix='C' THEN @C_value
        WHEN @Prefix='D' THEN @D_value
    END;
    FETCH NEXT FROM db_cursor INTO @Prefix,@A_value,@B_value, @C_value,@D_value 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

select * from @result