0

enter image description here

enter image description here

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.

enter image description here

Please provide the query

  • Please tag your DBMS, version included. – McNets Jun 14 at 19:59
0
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 > '' )
SELECT cte1.*
FROM cte1
JOIN cte2 ON cte1.ID = cte2.ID AND cte1.column_name = cte2.column_value;

fiddle

| improve this answer | |
  • Thank You Akina. I'm using SQL server, Is it possible to use without mention the column names – 0143.NetUser Jun 16 at 7:51
  • @0143.NetUser I'm using SQL server, Is it possible to use without mention the column names SQL server may use UNPIVOT. Check. Column names lists in FOR column_name IN (...) cannot be skipped. Unless you'll use dynamic SQL. – Akina Jun 16 at 8:01
  • Actual data "Table 1" contains 40 columns, there after user can add few more columns like "PTA_6,PTA_7..........PTA_20".So no need to add column names in UNPIVOT manually it should add column names dynamically.Is it possible? – 0143.NetUser Jun 16 at 8:16
  • @0143.NetUser user can add few more columns like This is VERY BAD practice. User must NOT have any DDL permissions, client code must NOT execute DDL. Normalize your structure to (ID - ParameterID - ParameterValue) where ParameterID is a reference to according Parameters table - and when user needs in "create more columns" he will simply add few rows into this Parameters table. Perform such normalization with both tables. And the query which solves your initial task will have very simple solution. – Akina Jun 16 at 8:20
  • :- I know the table structure,this is industrial machine data provided by supplier.so can't do change his table structure and user wont have DDL permission.SQL admin have a right to modify table.I have to create report using this table data only.That's what raised this question """Actual data "Table 1" contains 40 columns, there after user can add few more columns like""". – 0143.NetUser Jun 19 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.