How can I compare row values as column name to the another table and Get data (Sql Server-2017)? CREATE TABLE Table1(
ID int NOT NULL,
PTA_1 int NOT NULL,
PTA_2 int NOT NULL,
PTA_3 int NOT NULL,
PTA_4 int NOT NULL,
PTA_5 int NOT NULL,
DTA_1 int NOT NULL,
DTA_2 int NOT NULL,
DTA_3 int NOT NULL,
DTA_4 int NOT NULL,
DTA_5 int NOT NULL
)

INSERT INTO Table1 VALUES(1,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(2,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(3,1,0,1,0,0,0,1,0,0,0)
INSERT INTO Table1 VALUES(4,1,0,1,0,0,0,1,0,0,0)



CREATE TABLE Table2(
ID int  NOT NULL,
PartNum1 varchar(10),
PartNum2 varchar(10),
PartNum3 varchar(10),
PartNum4 varchar(10),
PartNum5 varchar(10),
DataNum1 varchar(10),
DataNum2 varchar(10),
DataNum3 varchar(10),
DataNum4 varchar(10),
DataNum5 varchar(10)
)

INSERT INTO Table2 VALUES(1,'PTA_1','PTA_2','PTA_3','','','DTA_1','DTA_2','DTA_3','DTA_4','')
INSERT INTO Table2 VALUES(2,'PTA_1','','','','','DTA_1','DTA_2','','','')
INSERT INTO Table2 VALUES(3,'PTA_1','PTA_2','','','','DTA_1','','','','')
INSERT INTO Table2 VALUES(4,'PTA_1','','','','','','','','','')


I have 2 tables as "Table1" and "Table2".
"Table1" is transaction table it contains zero's or 1's value and "Table2" contains "Table1" column names.
Compare "Table2" column name and get data as per relevant ID's.
Result should be like this. 



Please provide the query
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