1

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.

Jaster
  • 8,255
  • 3
  • 34
  • 60

2 Answers2

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • A word of caution. XML does not like column names with special characters and those that begin with numbers. – John Cappelletti Jun 30 '19 at 14:22
  • Thats amazing, but what It does, it creates one column per field I've used in the resultset. So I will have many columns with mostly null values. I'd like to have it "per row". I just edited my example to demonstrate – Jaster Jun 30 '19 at 15:02
  • 1
    @Jaster The solution provided will generate the updated desired results – John Cappelletti Jun 30 '19 at 15:15
  • 1
    @Jaster See the updated dbFiddle https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0006d5bc51d4fa85307492cf0122fa27 – John Cappelletti Jun 30 '19 at 15:16
  • @Jaster Happy to help. Just to be clear... You may have to rethink this approach on LARGE tables. I tend to use this technique to normalize smaller result sets. :) – John Cappelletti Jun 30 '19 at 15:23
  • What does `B` mean in `B(XMLData)` and why does the query keep working when I replace `B` by `random_word`? – Mason Mar 06 '23 at 09:56
  • @Mason B is just the alias for the CROSS APPLY. If you have 2016+ ... the JSON version is a nudge more performant https://stackoverflow.com/questions/72468392/how-to-transform-columns-to-rows-in-sql-server/72468619#72468619 – John Cappelletti Mar 06 '23 at 11:58
0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes that would work, but it's over simplified. Lets assume we have 30+ different columns and maybe even different tables (referenced in another field). I'm looking for some kind of join aiming at the structure. – Jaster Jun 30 '19 at 13:12
  • 2
    Then you'll need a lot of `CASE` expressions @Jaster. Otherwise, yes, you'll need Dynamic SQL; but this type of SQL normally points at a design flaw. – Thom A Jun 30 '19 at 13:23
  • I know it is/seems flaw design. And I assume I need dynamic sql... but I have no clue how to – Jaster Jun 30 '19 at 13:46