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.

TABLE A:
CONTRACTNO  QTY SUPPLIER    CDATE
----------  --- --------    -----
23231       12  LG          28-Nov-23
23232       2   SM          23-Nov-23
23233       44  AP          11-Dec-23
23234       9   BR          25-Dec-23
23235       22  SM          15-Dec-23
23236       7   AP          08-Dec-23
23237       1   LG          30-Nov-23

Expected Output:

Wk_27-Nov-23 Wk_04-Dec-23 Wk_11-Dec-23
-----------  -----------  -----------
13           7            66

where Wk_27-Nov-23 = Sum(Qty) from 27-Nov-23 to 03-Dec-23, next week from 04-Dec-23 to 10-Dec-23 and so on till the Monday before :enddate.

I have no idea where to start as I'm fairly new to Oracle SQL. So far, I have inserted Mondays between start and end dates into a Temp table using the below query but need to know how to apply this to SUM()/GROUP.

select MonDate from (select (sysdate-7 + rownum -1) MonDate
from all_objects 
where rownum <= (to_date('15-DEC-23', 'dd-mon-yy')) - (sysdate-7))
where rtrim(to_char(MonDate, 'DAY')) = 'MONDAY';

Thanks!

Solution

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:

Wk_27-Nov-23	Wk_04-Dec-23	Wk_11-Dec-23
13	7	66

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:

WEEK_START	TOTAL_QTY
2023-11-27 00:00:00	13
2023-12-04 00:00:00	7
2023-12-11 00:00:00	66

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