Join with dynamic Table and Column names

Question

I'm trying to join from a table where the tables and fields are defined within the data instead of keys. So here is what I have

Table Root:

ID | Table   | Field
---+---------+-----------
 1 |   Tab1  | Field1  
 2 |   Tab2  | Field2  
 3 |   Tab1  | Field2
 4 |   Tab3  | Field4  
 5 |   Tab1  | Field1

Tab1

ID | Field1  
---+---------
 1 |  A
 2 |  B  
 3 |  C  
 4 |  D

Tab2

ID | Field1 |Field2
---+--------+-----------
 1 |   X    |   Bla
 2 |   Y    |   123
 3 |   Z    |   456

Tab3 does not exist

I'd like to have a result like that one:

ID | Value
---+---------
 1 |  A
 2 |  123  
 3 |  NULL  -- Field does not match
 4 |  NULL  -- Tables does not exist
 5 |  NULL  -- ID does not exist

Basicly trying to join using the the ID trageting a dynamic table and field.

My Starting Point is somehwere around Here, but this is just for a single specific table. I can't figure out how to join dynamicly or if it even possible without dynamic sql like exec.

You'll need to use a `CASE` expression. What have you tried so far? This type of requirement, however, normally infers a design flaw in your database. — Thom A, Jul 04 '19 at 11:38
Case is not a solution, as there might be many many different combinations — Jaster, Jul 04 '19 at 12:02
Have a look at my answer [here](https://stackoverflow.com/a/74232389/12930883) , it will solve your issue. — RED-ONE, Oct 28 '22 at 08:22

score 0 · Answer 1 · answered Jul 04 '19 at 11:52

you could solve this with a case expression and subqueries, like this example

declare @root table (id int, [table] varchar(10), Field varchar(10))
declare @tab1 table (id int, Field1 varchar(10))
declare @tab2 table (id int, Field1 varchar(10), Field2 varchar(10))

insert into @root (id, [table], Field)
values (1, 'Tab1', 'Field1'), (2, 'Tab2', 'Field2'), (3, 'Tab1', 'Field2'), (4, 'Tab3', 'Field4'), (5, 'Tab1', 'Field1')

insert into @tab1 (id, Field1)
values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D')

insert into @tab2 (id, Field1, Field2)
values (1, 'X', 'Bla'), (2, 'Y', '123'), (3, 'Z', '456')

select r.id,
       case when r.[Table] = 'Tab1' and r.Field = 'Field1' then (select t1.Field1 from @tab1 t1 where t1.ID = r.ID)
            when r.[Table] = 'Tab2' and r.Field = 'Field1' then (select t2.Field1 from @tab2 t2 where t2.id = r.id)
            when r.[Table] = 'Tab2' and r.Field = 'Field2' then (select t2.Field2 from @tab2 t2 where t2.id = r.id)
       end as Value
from   @root r

the result is

id  Value   
--  ------- 
1   A   
2   123 
3   null    
4   null    
5   null

Case is not a solution, as there might be many many different combinations — Jaster, Jul 04 '19 at 12:02
The only other way will be `dynamic sql` but to build it you will also need `case expressions` I dont see any other way — GuidoG, Jul 04 '19 at 12:25

Join with dynamic Table and Column names

1 Answers1