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...

问题:

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.


回答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)') 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

回答2:

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;
  • 发表于 2019-07-05 10:50
  • 阅读 ( 187 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除