Merge two identical table structures with different data

Question

Edit: After trying to use the COALESCE method, I now see a problem when the data is repeated with the same data for each power category. Column 2 - power.

I created two temporary tables, both with the same table structure. These tables have several columns that can have the same values, and then several columns of values ​​that will have different numbers. Some of them will be NULL in one column, and not null in another. I want all the values ​​to be together, and on lines with the same site and installation, I would like the values ​​to be combined.

Here is an example of what the two tables might look like, and the result I would expect

TABLE 1:

SITE PLANT VALUE_1 VALUE 2 S1 P1 54 66 S1 P2 43 43 

TABLE 2:

 SITE PLANT VALUE_1 VALUE_2 S1 P1 33 43 S2 P1 34 22 

RESULT:

 SITE PLANT t1_VALUE_1 t1_VALUE_2 t2_VALUE_1 t2_VALUE2 S1 P1 54 66 33 43 S1 P2 43 43 NULL NULL S2 P1 NULL NULL 34 22 

My initial thoughts would be a complete mix. However, this does not work, because in your select statement you must specify where to get the columns, for example, site and plant; but to select t1.site and t2.site two columns will be created. The closest I received is the request below, however at any time there is a result in S2, which has a site and is not in S1, you get zero values ​​for S1 and S2.

 SELECT t1.Site, t1.Plant, t1.Value_1, t1.Value_2, t2.Value_1, t2.Value_2 FROM table1 t1 FULL JOIN table2 t2 ON t1.site = t2.site AND t1.plant = t2.plant 

Answer 1

Two tricks are needed to complete this request. The first is a FULL JOIN. A full join will allow you to join both tables and insert zeros in both tables if you do not agree with the join condition. The second is COALESCE, which will allow you to take the plant and site from what the table gives an entry for this row in the results.

 SELECT Coalesce(t1.Site,t2.Site) As Site, COALESCE(t1.Plant, t2.Plant) As Plant, t1.Value_1 As t1_Value_1, t1.Value_2 As t1_Value_2, t2.Value_1 As t2_Value_1, t2.Value_2 As t2_Value_2 FROM Table1 t1 FULL JOIN Table2 t2 ON t1.Site = t2.Site AND t1.Plant = t2.Plant 

Answer 2

You can use coalesce . It will return the first nonzero value from the parameters.

 SELECT coalesce(t1.Site, t2.Site) as Site, coalesce(t1.Plant, t2.Plant) as Plant,