有没有办法像下面这样在SQL中自动计算列名。
不可以，在SQL（不仅仅是Oracle SQL）中，您需要固定的已知列名数量，因此不可能使用静态SQL查询动态生成列。
如果要生成数据，请执行以下操作之一：
将数据生成为行（而不是列），并在用于访问数据库的任何第三方应用程序中透视结果。可以使用相关行生成器生成输出：

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

或：

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

其中，对于示例数据：

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;

两者输出：
| 材料|ISO年|ISO_周|每周_销售额|
| - -|- -|- -|- -|
| 一月|小行星2022|四十个|二百五十个|
| 一月|小行星2022|四十一|二百五十个|
| 一月|小行星2022|四十二|二百五十个|
| 一月|小行星2022|四十三个|二百五十个|
| 二月|小行星2022|四十六|500个|
| 二月|小行星2022|四十七|500个|
或者，如果您确实希望将值输出为列，则需要指定列（对于所有53个潜在ISO周，将为53列），可以使用以下命令执行此操作：

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
  )
)

或：

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
  )
)

这两个输出：
| 材料|ISO年|CW 01系列|CW 02型|CW 03系列|CW 40型|CW 41型|CW 42型|CW 43型|CW 44型|CW 45型|CW 46型|CW 47型|CW 48型|CW49| CW 50型|CW 51型|CW 52型|CW 53型|
| - -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|- -|
| 一月|小行星2022| * 空值 | 空值 | 空值 *| 二百五十个|二百五十个|二百五十个|二百五十个| * 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 *|
| 二月|小行星2022| * 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 | 空值 *| 500个|500个| * 空值 | 空值 | 空值 | 空值 | 空值 | 空值 *|
fiddle