How can I compare row values as column name to the another table and Get data (Sql Server-2017)?
SELECT t1.ID, t1.column_name, t1.column_value
FROM (
SELECT ID, 'PTA_1' column_name, PTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_2' column_name, PTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_3' column_name, PTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_4' column_name, PTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'PTA_5' column_name, PTA_5 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_1' column_name, DTA_1 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_2' column_name, DTA_2 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_3' column_name, DTA_3 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_4' column_name, DTA_4 column_value FROM Table1 UNION ALL
SELECT ID, 'DTA_5' column_name, DTA_5 column_value FROM Table1
) t1
JOIN (
SELECT ID, PartNum1 column_name FROM Table2 UNION ALL
SELECT ID, PartNum2 column_name FROM Table2 UNION ALL
SELECT ID, PartNum3 column_name FROM Table2 UNION ALL
SELECT ID, PartNum4 column_name FROM Table2 UNION ALL
SELECT ID, PartNum5 column_name FROM Table2 UNION ALL
SELECT ID, DataNum1 column_name FROM Table2 UNION ALL
SELECT ID, DataNum2 column_name FROM Table2 UNION ALL
SELECT ID, DataNum3 column_name FROM Table2 UNION ALL
SELECT ID, DataNum4 column_name FROM Table2 UNION ALL
SELECT ID, DataNum5 column_name FROM Table2
) t2 USING (ID, column_name);

fiddle
Move subqueries into CTEs if DBMS and its version allows.
Use UNPIVOT or similar instead of UNION ALL if DBMS and its version allows.



I'm using SQL server, Is it possible to use without mention the column names

WITH 
cte1 AS ( SELECT ID, column_name, column_value
          FROM Table1
          UNPIVOT ( column_value FOR column_name IN ( PTA_1, PTA_2, PTA_3, PTA_4, PTA_5, DTA_1, DTA_2, DTA_3, DTA_4, DTA_5 ) 
                  ) AS unpvt ),
cte2 AS ( SELECT ID, column_value
          FROM Table2
          UNPIVOT ( column_value FOR column_name IN ( PartNum1, PartNum2, PartNum3, PartNum4, PartNum5, DataNum1, DataNum2, DataNum3, DataNum4, DataNum5 ) 
                  ) AS unpvt
          WHERE column_value &gt; '' )
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.ID = cte2.ID AND cte1.column_name = cte2.column_value;

fiddle