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

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.

CodePudding user response：

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)

CodePudding user response：

You need to unpivot and join the input data:

Tables:

SELECT *
INTO TableA
FROM (VALUES
   (1, 'a', 'aa', 'aaa'),
   (2, 'b', 'bb', 'bbb')   
) v (ID, COL1, COL2, COL3)

SELECT *
INTO TableB
FROM (VALUES
   (1, 'COL1'),
   (2, 'COL3')   
) v (AID, COL)

Statement:

SELECT b.AID, v.VALUE
FROM TableB b
JOIN TableA a ON b.AID = a.ID
CROSS APPLY (VALUES
   ('COL1', a.COL1),
   ('COL2', a.COL2),
   ('COL3', a.COL3)
) v (COL, [VALUE]) 
WHERE v.COL = b.COL