CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
TSR
TSR

Reputation: 724

SQL Server, Dynamically join using the column name and value from same table with a different table

I have two tables as below that I am looking to join dynamically using the ColVal in TMaster with ColName in TMaster which is the name of the column to match with in TTable1.

TMaster:

enter image description here

TTable1:

enter image description here

So, final result would be the data from TTable1 that matches the colName and ColVal in TMaster. How do I achieve this?

select tt.Name 
from TMaster tm 
join TTable1 tt on TMaster.ColVal = tt.[ColName from TMaster should be used here]

Upvotes: 0

Views: 482

Answers (2)

SQLpro
SQLpro

Reputation: 5113

Why not :

SELECT tt.Name 
FROM   TMaster AS tm
       JOIN TTable1 AS tt 
          ON tm.ColVal = CASE ColVal 
                             WHEN 1 THEN tt.Col1
                             WHEN 2 THEN tt.Col2
                             WHEN 3 THEN tt.Col3
                          END

Upvotes: 0

Charlieface
Charlieface

Reputation: 71263

You can formulate your query in the following manner

SELECT tt.Name 
FROM TMaster tm
JOIN TTable1 tt ON 
    tm.ColName = 'Col1' AND tm.ColVal = tt.Col1 OR
    tm.ColName = 'Col2' AND tm.ColVal = tt.Col2 OR
    tm.ColName = 'Col3' AND tm.ColVal = tt.Col3 OR
....

It's not going to be very performant though.

If you do not even know your column names (why that should be, I don't know) then you need dynamic SQL.

DECLARE @sql nvarchar(max) = '
SELECT tt.Name 
FROM TMaster tm
JOIN TTable1 tt ON 
' +
(
    SELECT STRING_AGG(CAST(
'    tm.ColName = ' + QUOTENAME(c.name, '''') + ' AND tm.ColVal = tt.' + QUOTENAME(c.name)
    AS nvarchar(max)), N' OR
'
    )
    FROM sys.columns
    WHERE OBJECT_NAME(object_id) = 'TTable1'
);

PRINT @sql; -- for testing

EXEC sp_executesql @sql;

Upvotes: 0

Related Questions