calculate column names automatically sql

Question

Is there a way to calculate column names automatically in SQL like below. I need top calculate the Calendar weeks based on from and to date and distribute evenly

Material	From	To	Sales
M01	03.10.2022	31.10.2022	1000
M02	14.11.2022	28.11.2022	1000

Expected output

CW =calendar week

Material	Cw40	CW41	Cw42	CW43	CW44	CW45	CW46	CW47
M01	250	250	250	250
M02							500	500

What is your actual issue? Distribution? Dynamic column names? Calculation of calendar week? — astentx, Oct 13 '22 at 12:14

MT0 · Answer 1 · 2022-10-20T17:02:39.037

Is there a way to calculate column names automatically in SQL like below.

No, in SQL (not just Oracle SQL) you needs a fixed, known number of column names so it is impossible to dynamically generate columns with a static SQL query.

If you want to generate the data then either:

Generate the data as rows (rather than columns) and pivot the result in whatever third-party application you are using to access the database. You can generate the output using a correlated row-generator:

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

or:

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

Which, for the sample 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;

Both output:

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

Or, if you did want to output the values as columns then you need to specify the columns (which would be 53 columns for all 53 potential ISO weeks) and can do that using:

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

or:

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

Which both output:

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

fiddle

@Steve You can try a recursive sub-query factoring clause (recursive `WITH` clause) or else use `SELECT ... UNION ALL SELECT ... UNION ALL ...` with 52 `UNION ALL`s? — MT0, Oct 20 '22 at 15:44
thanks, but how with recursive WITH clause, (atleast the first part - without transposing) — Steve, Oct 20 '22 at 16:13
Anyother way to do it without using recursive/connectby, so that it works it all in database — Steve, Oct 20 '22 at 18:21
@Steve All RDBMS have slightly different syntaxes; you can use recursive `WITH` clauses in many databases so that is probably likely to be the most portable way to write the query however you are very unlikely to find one query that will run on every RDBMS as, amongst other things, they will all have different names for their functions. — MT0, Oct 20 '22 at 18:28
anychance to do with window function , working with mysql older version so — Steve, Oct 20 '22 at 19:27
@Steve [Ask a new question](https://stackoverflow.com/questions/ask) and tag it with MySQL (and not Oracle) if you are using MySQL (and not Oracle) and then you might get an answer about the correct technology. — MT0, Oct 20 '22 at 23:42
@Steve Or search the internet for "MySQL row generator" (and you'll probably end up at [this question](https://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql)) — MT0, Oct 20 '22 at 23:48

calculate column names automatically sql

1 Answers1