select
(select top 1 code from table2 where value=a.col1) as col1,
(select top 1 code from table2 where value=a.col2) as col2,
(select top 1 code from table2 where value=a.col3) as col3
from table1 a
select c1=(select [value] from table2 where code=a.col1),
c2=(select [value] from table2 where code=a.col2),
c3=(select [value] from table2 where code=a.col3)
from table1
select (select code from t2 where value=t1.col1) code1,
(select code from t2 where value=t1.col2) code2,
(select code from t2 where value=t1.col3) code3
from t1