Reference table name column in another table in join - SQL Server

Question

I have following condition :

Table-1
col1   col2    col3
1         40     100 (identity column value for tblABC)
2         41     101 (identity column value for tblDEF)

Table-2
col1       col2        col3
40        tblABC     tblABCPrimaryKey
41        tblDEF     tblDEFPrimaryKey

========= Different tables ==============
tblABC
tblPrimaryKeyId col2
100 VALUE

tblDEF
tblPrimaryKeyId col2
101 VALUE

I need to get below column in join
         select Table-1.col1,
                   Table-2.col2,
                   [ tblABC.col2 OR tblDEF.col2 and so on depending on the table]
from Table-1
INNER JOIN (join goes here)

I want to get
col1 from Table-1,
col2 from Table-2 where col2 of Table-1 matches col1 of Table-2,
col2 from (TABLES IN COL2 of Table-2 where col3 of Table-2 matches column in those tables in col-2 [Different tables])

Please help.

can you post that as formatted text instead of all the HTML tags and explain what your end result should be? — scsimon
Hi, I do not know how to format the text. You can find the attchement image for what am I looking for. Thanks. — Sandipan
Welcome to Stack Overflow. Your question doesn't include enough useful detail for us to help you. Please take a moment to read these two links, then consider editing your question if you're still looking for help. stackoverflow.com/help/how-to-ask and, regarding posting pictures, meta.stackoverflow.com/a/285557/5790584 — Eric Brandt
I haven't understand clearly what you are asking, but this might help you.. stackoverflow.com/questions/10195451/… — CR241
Sorry for the confusion, Please find the edited text if it helps. Thanks. — Sandipan

Adam Adam · Accepted Answer · 2018-08-01T01:00:20

DECLARE @sql NVARCHAR(MAX) = '
SELECT Table1.col1
    , Table2.col2,
    , COALESCE(';

SELECT
    @sql = @sql + #Table2.col2 + '.col2, '
FROM #Table2;

SET @sql = @sql + ' NULL)
INNER JOIN Table2
    ON Table1.col2 = Table2.col1';

SELECT
    @sql = @sql + '
LEFT JOIN ' + col2 + ' 
    ON Table2.col2 = ''' + col2 + ''' 
    AND Table1.col3 = ' + col2 + '.col1'
FROM #Table2

EXEC sys.sp_executesql @sql;

Reference table name column in another table in join - SQL Server

1 Answers