Need to get the value from a column whose column name is based on a value in another table

Table A has columns ID, COL1, COL2, COL3. Table B has columns AID, ColumnName.

I need to get the [ColumnName] value in Table A based on the value of [ColumnName] in Table B. In the example below:

For ID 1, I need to get the value of column COL1 (This is the value of [ColumnName] for AID 1 in Table B).

For ID 2, I need to get the value of column COL3 (This is the value of [ColumnName] for AID 2 in Table B).

Table A

ID COL1 COL2 COL3
1  a    aa   aaa
2  b    bb   bbb

Table B

AID ColumnName
1   COL1
2   COL3

Desired Result:

ID VALUE
1  a
2  bbb

How can I do that ? Thank you.

Answer

Unpivot then join

drop table t
go
drop table t1
go
create table t
(ID int, COL1 varchar(10), COL2 varchar(10), COL3 varchar(10))
go
create table t1
(AID int,ColumnName varchar(10));
go
insert into t values
(1 , 'a',    'aa',   'aaa'),
(2 , 'b',    'bb',   'bbb')
go

insert into t1 values
(1 ,  'COL1'),
(2 ,  'COL3')
go

with cte as
(select id, u.col, u.val
from t
unpivot
(
  val
  for col in (col1, col2, col3)
) u
)
select cte.id,cte.val
from cte
join t1 on 
    t1.aid = cte.id and
    t1.columnname = cte.col

go

id          val
----------- ----------
1           a
2           bbb

(2 row(s) affected)