Q:

How to select a value from column in table, using column name from other table dynamically (reflection)

I am trying to select a value in a table with multiple columns, based on the column id in another table like this
foo:
+-------+--------+--------+--------+
|  id   |  tag   | col1   |  col2  |
+-------+--------+--------+--------+
|   1   |   XX   |    1   |    2   |
+-------+--------+--------+--------+
bar:
+-------+--------+--------+
| id    | field  |  tag   |
+-------+--------+--------+
|   1   |  col1  |   XX   |
+-------+--------+--------+
|   2   |  col2  |   XX   |
+-------+--------+--------+

I guess what I am looking for is a mechanism like reflection, where I can map to a property value based on the name of that property, without having to know about the class or table ahead of time, since the columns can change over time, the resulting table should look something like this
foobar:
+--------+--------+
|  tag   | value  |
--------++--------+
|   XX   |    1   |
--------++--------+
|   XX   |    2   |
--------++--------+

I tried something like the below code, however it gives me the column id in both result columns, I need the value of that particular field not the name of the column. 
declare @SQL nvarchar(max)
declare @Pattern nvarchar(100)

set @Pattern = 'select bar.field, ''foo.[COL_NAME]'' as CurrentValue FROM bar INNER JOIN foo ON foo.[tag] = bar.[tag]'

select @SQL = stuff((select ' union all '+replace(@Pattern, '[COL_NAME]', field)
                 from bar
                 for xml path(''), type).value('.', ' nvarchar(max)'), 1, 11, '')

exec (@SQL)

A:

Don't think it's possible to do it dynamically with a single SQL statement.   But you could do it with dynamic sql. 
With the given data set, the simplest way to get the desired result would be with a case statement. 
select foo.tag, 
case 
    when bar.field = 'col1' then foo.col1
    when bar.field = 'col2' then foo.col2
    else null
end as value
from foo
    inner join bar on foo.tag = bar.tag

Just the when...then... parts are dynamic but the rest is static.   So loop over the bar table to build up the dynamic parts, and then concat it all together. 
declare @sql varchar(2000)
set @sql=''
select @sql = @sql +' when bar.field='''+field+''' then foo.'  + field
from (select distinct field from bar) sub

--print @sql

set @sql = 'select foo.tag, 
case ' + @sql  + ' 
    else null
end as value
from foo
    inner join bar on foo.tag = bar.tag'

--print @sql

exec(@sql)

