Oracle query - display column values as column names for sum by date range

I need to write an oracle query to find total contract quantities ordered each week (Monday to Sunday) between 2 given dates (say, :startdate = '28-Nov-23' and :enddate = '15-Dec-23') and display the Date values as column names.

Use conditional aggregation:

SELECT SUM(
         CASE
         WHEN cdate >= DATE '2023-11-27' AND cdate < DATE '2023-12-04'
         THEN qty
         END
       ) AS "Wk_27-Nov-23",
       SUM(
         CASE
         WHEN cdate >= DATE '2023-12-04' AND cdate < DATE '2023-12-11'
         THEN qty
         END
       ) AS "Wk_04-Dec-23",
       SUM(
         CASE
         WHEN cdate >= DATE '2023-12-11' AND cdate < DATE '2023-12-18'
         THEN qty
         END
       ) AS "Wk_11-Dec-23"
FROM   table_a;

Which, for the sample data:

CREATE TABLE TABLE_A (CONTRACTNO, QTY, SUPPLIER, CDATE) AS
SELECT 23231, 12, 'LG', DATE '2023-11-28' FROM DUAL UNION ALL
SELECT 23232,  2, 'SM', DATE '2023-11-23' FROM DUAL UNION ALL
SELECT 23233, 44, 'AP', DATE '2023-12-11' FROM DUAL UNION ALL
SELECT 23234,  9, 'BR', DATE '2023-12-25' FROM DUAL UNION ALL
SELECT 23235, 22, 'SM', DATE '2023-12-15' FROM DUAL UNION ALL
SELECT 23236,  7, 'AP', DATE '2023-12-08' FROM DUAL UNION ALL
SELECT 23237,  1, 'LG', DATE '2023-11-30' FROM DUAL;

Outputs:

In SQL (not just Oracle), a statement must have a known, fixed number of columns and you cannot dynamically generate the aliases for those columns within the query. Therefore, if you do not want to hardcode the dates into the aliases then it is impossible with a static query.

If you do want dynamic columns then, instead, you should consider generating the output as rows:

SELECT TRUNC(cdate, 'IW') AS week_start,
       SUM(qty) AS total_qty
FROM   table_a
WHERE  cdate >= DATE '2023-11-27'
AND    cdate <  DATE '2023-12-25'
GROUP BY TRUNC(cdate, 'IW')
ORDER BY week_start;

Which, for the sample data, outputs:

And pivoting the output in whatever third-party client application (i.e. C#, PHP, Java, Python, etc.) that you are using to connect to the database and you can set the column headers in that third-party software.

The alternative is you dynamically generate the SQL but its simpler to just accept that SQL is not the tool for the output that you want and to do it in a third-party application that can easily cope with the task of transposing rows and columns.

fiddle