Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (Material, From_dt, To_dt, Sales) AS
SELECT 'M01', DATE '2022-10-03', DATE '2022-10-31', 1000 FROM DUAL UNION ALL
SELECT 'M02', DATE '2022-11-14', DATE '2022-11-28', 1000 FROM DUAL;
2 rows affected

SELECT t.material,
       w.iso_year,
       w.iso_week,
       w.weekly_sales
FROM   table_name t
       CROSS APPLY (
         SELECT TO_NUMBER(
                  TO_CHAR(
                    TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                    'IYYY'
                  )
                ) AS iso_year,
                TO_NUMBER(
                  TO_CHAR(
                    TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                    'IW'
                  )
                ) AS iso_week,
                ( LEAST(
                    TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * LEVEL,
                    to_dt
                  )
                  - GREATEST(
                    TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                    from_dt
                  )
                ) / (to_dt - from_dt) * sales AS weekly_sales
         FROM   DUAL
         CONNECT BY TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL-1) < to_dt
       ) w


MATERIAL
ISO_YEAR
ISO_WEEK
WEEKLY_SALES


M01
2022
40
250

M01
2022
41
250

M01
2022
42
250

M01
2022
43
250

M02
2022
46
500

M02
2022
47
500


WITH data (from_dt, dt, to_dt, material, sales) AS (
  SELECT from_dt, from_dt, to_dt, material, sales
  FROM   table_name
UNION ALL
  SELECT from_dt,
         TRUNC(dt + INTERVAL '7' DAY, 'IW'),
         to_dt,
         material,
         sales
  FROM   data
  WHERE  TRUNC(dt + INTERVAL '7' DAY, 'IW') < to_dt
)
SELECT material,
       TO_NUMBER(TO_CHAR(dt, 'IYYY')) AS iso_year,
       TO_NUMBER(TO_CHAR(dt, 'IW')) AS iso_week,
       ( LEAST(dt + INTERVAL '7' DAY, to_dt) - dt)
         / (to_dt - from_dt) * sales AS weekly_sales
FROM   data


MATERIAL
ISO_YEAR
ISO_WEEK
WEEKLY_SALES


M01
2022
40
250

M02
2022
46
500

M01
2022
41
250

M02
2022
47
500

M01
2022
42
250

M01
2022
43
250


SELECT *
FROM   (
  SELECT t.material,
         w.iso_year,
         w.iso_week,
         w.weekly_sales
  FROM   table_name t
         CROSS APPLY (
           SELECT TO_NUMBER(
                    TO_CHAR(
                      TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                      'IYYY'
                    )
                  ) AS iso_year,
                  TO_NUMBER(
                    TO_CHAR(
                      TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                      'IW'
                    )
                  ) AS iso_week,
                  ( LEAST(
                      TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * LEVEL,
                      to_dt
                    )
                    - GREATEST(
                      TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL - 1),
                      from_dt
                    )
                  ) / (to_dt - from_dt) * sales AS weekly_sales
           FROM   DUAL
           CONNECT BY TRUNC(from_dt, 'IW') + INTERVAL '7' DAY * (LEVEL-1) < to_dt
         ) w
)
PIVOT (
  SUM(weekly_sales)
  FOR iso_week IN (
     1 AS cw01,
     2 AS cw02,
     3 AS cw03,
    -- ...
    40 AS cw40,
    41 AS cw41,
    42 AS cw42,
    43 AS cw43,
    44 AS cw44,
    45 AS cw45,
    46 AS cw46,
    47 AS cw47,
    48 AS cw48,
    49 AS cw49,
    50 AS cw50,
    51 AS cw51,
    52 AS cw52,
    53 AS cw53
  )
)


MATERIAL
ISO_YEAR
CW01
CW02
CW03
CW40
CW41
CW42
CW43
CW44
CW45
CW46
CW47
CW48
CW49
CW50
CW51
CW52
CW53


M01
2022
null
null
null
250
250
250
250
null
null
null
null
null
null
null
null
null
null

M02
2022
null
null
null
null
null
null
null
null
null
500
500
null
null
null
null
null
null


WITH data (from_dt, dt, to_dt, material, sales) AS (
  SELECT from_dt, from_dt, to_dt, material, sales
  FROM   table_name
UNION ALL
  SELECT from_dt,
         TRUNC(dt + INTERVAL '7' DAY, 'IW'),
         to_dt,
         material,
         sales
  FROM   data
  WHERE  TRUNC(dt + INTERVAL '7' DAY, 'IW') < to_dt
)
SELECT *
FROM   (
  SELECT material,
         TO_NUMBER(TO_CHAR(dt, 'IYYY')) AS iso_year,
         TO_NUMBER(TO_CHAR(dt, 'IW')) AS iso_week,
         ( LEAST(dt + INTERVAL '7' DAY, to_dt) - dt)
           / (to_dt - from_dt) * sales AS weekly_sales
  FROM   data
)
PIVOT (
  SUM(weekly_sales)
  FOR iso_week IN (
     1 AS cw01,
     2 AS cw02,
     3 AS cw03,
    -- ...
    40 AS cw40,
    41 AS cw41,
    42 AS cw42,
    43 AS cw43,
    44 AS cw44,
    45 AS cw45,
    46 AS cw46,
    47 AS cw47,
    48 AS cw48,
    49 AS cw49,
    50 AS cw50,
    51 AS cw51,
    52 AS cw52,
    53 AS cw53
  )
)


MATERIAL
ISO_YEAR
CW01
CW02
CW03
CW40
CW41
CW42
CW43
CW44
CW45
CW46
CW47
CW48
CW49
CW50
CW51
CW52
CW53


M01
2022
null
null
null
250
250
250
250
null
null
null
null
null
null
null
null
null
null

M02
2022
null
null
null
null
null
null
null
null
null
500
500
null
null
null
null
null
null
MATERIAL	ISO_YEAR	ISO_WEEK	WEEKLY_SALES
M01	2022	40	250
M01	2022	41	250
M01	2022	42	250
M01	2022	43	250
M02	2022	46	500
M02	2022	47	500
MATERIAL	ISO_YEAR	CW01	CW02	CW03	CW40	CW41	CW42	CW43	CW44	CW45	CW46	CW47	CW48	CW49	CW50	CW51	CW52	CW53
M01	2022	null	null	null	250	250	250	250	null	null	null	null	null	null	null	null	null	null
M02	2022	null	null	null	null	null	null	null	null	null	500	500	null	null	null	null	null	null