Sql: Select Values By Column Name

I'd like to fetch values form a table, but the reference is the column name of the destination table instead of a key - yes, bad design. To be honest, I have no clue where to start

Solution 1:

Here is one approach that does NOT require Dynamic SQL. That said, I suspect dynamic SQL and/or UNPIVOT would be more performant.

• Cross Apply B will convert the record to XML

• Cross Apply C will consume the B's XML and UNPIVOT the record

• Then it is a small matter to join the Source table on ID and Item

Example dbFiddle

Select A.[ID]
      ,C.*From  YourTable A
 Cross Apply ( values (cast((Select A.*for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value= xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)','varchar(100)') notin ('Id','Other-Columns','To-Exclude')
             ) C
 Join Source D on A.ID=D.ID and C.Item=D.TargetField

Returns

ID  Item    Value
1   Field1  A1   Field2  B2   Field2  C
3   Field1  X

Solution 2:

You can use a case expression:

select s.id,
       (casewhen s.targetfield = 'field1' then r.field1when s.targetfield = 'field2' then r.field2end)
from source s join
     referenced r
     on s.id = r.id;