sql - Select dynamic column based on value of another column? -

given following table, dynamically select name1 or name2 field based on value of whichname column.

id  name1   name2   whichname   somevalue 1   bob     james   1           blue 2           steve   2           horse 3   fred            1           radish 

the results should this:

id  name    somevalue 1   bob     blue 2   steve   horse 3   fred    radish 

dbms caché

you can case:

select id,        (case when whichname = 1 name1 else name2 end) name,        somevalue <table>; 

note: if prefer name1, unless null, can use coalesce():

select id, coalesce(name1, name2) name, somevalue <table>;