Dynamically returned count columns based on date

Question

I have a table with the following 2 relevant fields :-

QUOTEID  REG_DATE
001      02/10/2017
002      09/10/2017
003      10/10/2017
004      13/10/2017
005      13/10/2017
006      17/10/2017

Is it possible to have Oracle output the above but grouped and counted by week (Saturday to Friday), with the column name being the date of the relevant Friday? So, using the data above, the output would be :-

20/10/2017 13/10/2017 06/10/2017 
1          5          1

Yes its possible using a calendar table, pivot and plsql and dynamic query — Sudipta Mondal, Oct 25 '17 at 13:07
It is possible, but not recommended. The only meaningful use is for reporting, and if so, then you should use the proper reporting tool to do the pivoting - it will be more flexible than SQL. Otherwise you will need to use dynamic SQL. What is wrong, though, with showing the same data in two COLUMNS and several ROWS (i.e., without pivoting it) - even for reporting purposes? — , Oct 25 '17 at 13:16

score 1 · Accepted Answer · answered Oct 25 '17 at 13:17

There are already many questions about dynamic pivot, for instance this: Dynamic pivot in oracle sql.

This query demonstrates only how to find "relevant friday" and pivot data if you know all dates:

select * 
  from (select quoteid, 
               to_char(trunc(reg_date + 2, 'iw') + 4, 'dd/mm/yyyy') friday
          from t)
  pivot (count(quoteid) for friday in ('20/10/2017', '13/10/2017', '06/10/2017'))

Test:

with t(quoteid, reg_date) as (
    select '001', date '2017-10-02' from dual union all
    select '002', date '2017-10-09' from dual union all
    select '003', date '2017-10-10' from dual union all
    select '004', date '2017-10-13' from dual union all
    select '005', date '2017-10-13' from dual union all
    select '006', date '2017-10-17' from dual)
select * 
  from (select quoteid, 
               to_char(trunc(reg_date + 2, 'iw') + 4, 'dd/mm/yyyy') friday
          from t)
  pivot (count(quoteid) for friday in ('20/10/2017', '13/10/2017', '06/10/2017'))

Result:

'20/10/2017' '13/10/2017' '06/10/2017'
------------ ------------ ------------
           1            4            1

Dynamically returned count columns based on date

1 Answers1