SQL & PL/SQL

Hello everyone,

I have a few questions about a PIVOT query I'm trying to write. Imagine the following basic scenario.

CREATE TABLE src
(
  id NUMBER(10),
  creation_date DATE, 
  grp VARCHAR(10)
);
insert into src(id, creation_date, grp) 
SELECT level, 
       TO_DATE('01/01/2019', 'DD/MM/YYYY') + ROUND(DBMS_RANDOM.VALUE (1, SYSDATE - TO_DATE('01/01/2019', 'DD/MM/YYYY'))),  
       'A' || ROUND(DBMS_RANDOM.VALUE (1, 9))
  FROM dual
 CONNECT BY LEVEL <= 100;
COMMIT;

I have tried to group by grp and have a sum per week in a different column each. I have created the following query.

WITH get_counts AS
(
SELECT TO_CHAR(creation_date, 'WW') AS week_number, 
       grp, 
       COUNT(id) AS cnt
  FROM src
 GROUP BY TO_CHAR(creation_date, 'WW'), grp 
)
SELECT *
  FROM get_counts c
 PIVOT (
   SUM(cnt)
   FOR week_number IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
 )
 ORDER BY grp
;

But for sure, something is fishy. But I don't see what. Can anyone help me understand my mistakes?

This query works a little bit better.

SELECT grp, 
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 1 THEN id END) AS cnt_01, 
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 2 THEN id END) AS cnt_02,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 3 THEN id END) AS cnt_03,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 4 THEN id END) AS cnt_04,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 5 THEN id END) AS cnt_05,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 6 THEN id END) AS cnt_06,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 7 THEN id END) AS cnt_07,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 8 THEN id END) AS cnt_08,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 9 THEN id END) AS cnt_09,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 10 THEN id END) AS cnt_10,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 11 THEN id END) AS cnt_11,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 12 THEN id END) AS cnt_12,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 13 THEN id END) AS cnt_13,
       COUNT(CASE WHEN TO_CHAR(creation_date, 'WW') = 13 THEN id END) AS cnt_14
  FROM src
 GROUP BY grp ;

I has the right totals. But, not of these two queries are what I need. I'm trying to have ONLY the last 10 weeks. So, the colum name should reflect the correct week number. Is this possible with Oracle 11g or 12c?

Regards,