Add date based on two column values and transpose other columns

I have the next two tables:

Table A

Product	Plant	ProductionWeek1	...	ProductionWeek11
10011	ABC32	6742	...	3645
10011	DEF10	3579	...	8761
45891	GHI22	8945	...	1556

Table B

Product	Plant	SalesWeek1	...	SalesWeek11
10011	ABC32	425	...	364
10011	DEF10	879	...	501
45891	GHI22	904	...	395

I would want a table like this, when the first week start in 01/01/2001, the second week would be 08/01/2001 ans so on, it could be any date the important thing is that the difference between dates have to be one week:

Product	Plant	Production	Sales	Date
10011	ABC32	6742	425	01/01/2001
10011	ABC32	...	...	...
10011	ABC32	3645	364	18/06/2001
10011	DEF10	3579	879	01/01/2001
10011	DEF10	...	...	...
10011	DEF10	8761	501	18/06/2001
45891	GHI22	8945	904	01/01/2001
45891	GHI22	...	...	...
45891	GHI22	1556	395	18/06/2001

I try the next code to transpose the columns for the two columns:

CREATE TABLE TABLE_A1 AS (SELECT P.PRODUCT AS PRODUCT_1, 
                              P.PLANT AS PLANT_1, 
                              P.PRODUCTION AS PRODUCTION_1
                              FROM (SELECT PRODUCT, PLANT, ProductionWeek1 FROM TableA 
                                    UNION 
                                    SELECT PRODUCT, PLANT, ProductionWeek2 FROM TableA
                                    UNION 
                                    ...
                                    SELECT PRODUCT, PLANT, ProductionWeek11 FROM TableA) p;

CREATE TABLE TABLE_B1 AS (SELECT s.PRODUCT AS PRODUCT_2, 
                              s.PLANT AS PLANT_2, 
                              s.SALES AS SALES_2
                              FROM (SELECT PRODUCT, PLANT, SalesWeek1 FROM TableB 
                                    UNION 
                                    SELECT PRODUCT, PLANT, SalesWeek2 FROM TableB
                                    UNION 
                                    ...
                                    SELECT PRODUCT, PLANT, SalesWeek11 FROM TableB) s;

Then try to put all together

CREATE TABLE FINAL_TABLE AS ( 
                            SELECT PRODUCT_1 AS PRODUCT,
                            PLANT_1_CONSUMOS AS PLANT, 
                            PRODUCTION_1,
                            SALES_2
                            FROM TABLE_A1
                            INNER JOIN TABLE_B1 
                            ON TABLE_B1.PRODUCT_2=TABLE_A1.PRODUCT_1
                            );

But I have a table like this:

Product	Plant	Production	Sales
10011	ABC32	6742	425
10011	ABC32	3645	425
10011	ABC32	3645	364

The table is in disorder due to in the creation from TABLE_A1 and TABLE_B1 the union was in disorder too, so for that reason is important the date, for example I expect an union like this:

Product	Plant	Production	Sales
10011	ABC32	6742	425
10011	ABC32	...	...
10011	ABC32	3645	364

But I got the next table

Product	Plant	Production	Sales
10011	ABC32	3645	364
10011	ABC32	...	...
10011	ABC32	6742	425

where the value for the last week is in other row (not necesarry the first). Any help?

Solution

The current tables can be described as being "pivoted" because you have data representing a single week per column. To make sense of this you first need to "unpivot" that data and while you can use unions to do this, it is more efficient to simply cross join the source table to a set of rows representing each week (i.e. 11 rows). Then once cross joined use case expressions to move the data into the needed number of columns. e.g. the unpivoted result should look like this (ignoring weeks 2 to 10):

product plant   week    type    value
10011   ABC32   1   production  6742
10011   DEF10   1   production  3579
45891   GHI22   1   production  8945
10011   ABC32   11  production  3645
10011   DEF10   11  production  8761
45891   GHI22   11  production  1556
10011   ABC32   1   sales   425
10011   DEF10   1   sales   879
45891   GHI22   1   sales   904
10011   ABC32   11  sales   364
10011   DEF10   11  sales   501
45891   GHI22   11  sales   395

Once the data is available in this form it is a relatively simple task to produce a combination of the source columns by week. Note also that in the 11 weekly rows to be used it is also possible to supply the needed dates.

nb as you have not specified which rdbms is being used the following is in Postgres syntax but aside from the ::date syntax used the rest is generic SQL.

with w as (
          select 1 as week, '2001-01-01'::date as week_date union all
          /*   other weeks */
          select 11 as week, '2001-06-18'::date as week_date
        )
, unpiv as (
        select t.Product, t.Plant, w.week, w.week_date, 'production' as type
            , case
               when w.week = 1 then t.ProductionWeek1
               /* Other weeks */
               when w.week = 11 then t.ProductionWeek11
            end as value
        from Production t
        cross join w
        union all
        select t.Product, t.Plant, w.week, w.week_dat, 'sales' as type
            , case
               when w.week = 1 then t.SalesWeek1
               /* Other weeks */
               when w.week = 11 then t.SalesWeek11
            end as value
        from Sales t
        cross join w
        )
select
      u.Product, u.Plant
    , max(case when type = 'production' then value end) as production
    , max(case when type = 'sales' then value end) as sales
    , week, week_date
from unpiv u
group by
      u.Product, u.Plant
    , week, week_date
order by
      u.Product, u.Plant
    , week, week_date

product	plant	production	sales	week	week_date
10011	ABC32	6742	425	1	2001-01-01
10011	ABC32	3645	364	11	2001-06-18
10011	DEF10	3579	879	1	2001-01-01
10011	DEF10	8761	501	11	2001-06-18
45891	GHI22	8945	904	1	2001-01-01
45891	GHI22	1556	395	11	2001-06-18

fiddle

and here is a second (only slightly different) approach: https://dbfiddle.uk/pC0wmedZ