CODEWITHSUNDEEP
sqlsql-server
Jaster
Jaster

Reputation: 8581

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; could you give me some directions pelase?

Here is what I have

'Source' Table

ID | TargetField
---+-------------
 1 |   Field1   
 1 |   Field2   
 2 |   Field2   
 3 |   Field1

Rerenced Table:

ID | Field1 | Field2
---+--------+---------
 1 |   A    | B
 2 |   R    | C
 3 |   X    | D

The result would be this:

ID | TargetField | Value
---+-------------+-------
 1 |    Field1   |   A  
 1 |    Field2   |   B  
 2 |    Field2   |   C  
 3 |    Field1   |   X

As said, no idea how to get started... Am I looking at some dynamic SQL?

EDIT: The example is quite simplified, so switch/case will not work for me. I'd like to go for dynamic sql.

Upvotes: 1

Views: 1480

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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)') not in ('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  A
1   Field2  B
2   Field2  C
3   Field1  X

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use a case expression:

select s.id,
       (case when s.targetfield = 'field1' then r.field1
             when s.targetfield = 'field2' then r.field2
        end)
from source s join
     referenced r
     on s.id = r.id;

Upvotes: 0

Related Questions