Forum Stats

  • 3,750,348 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

PIVOT vs GROUP BY

user13117585
user13117585 Member Posts: 640 Bronze Badge
edited Apr 6, 2019 11:57AM in 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,

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Apr 5, 2019 4:27PM Accepted Answer

    Hi,

    user13117585 wrote:Ok,Thank you Frank Kulash for your answer. I was afraid of that actually. But since I don't really want to use dynamic SQL,

    Good idea!    Dynamic SQL is often not worth the trouble.

     I'd stick to the solution you suggest and put a name for each column. So, instead of week number, I think I'll try to have something likeTHREE_WEEKS_AGO, TWO_WEEKS_AGO, LAST_WEEK.Now, if I change my sample data like this:
    1. TRUNCATETABLEsrc;
    2. insertintosrc(id,creation_date,grp)
    3. SELECTlevel,
    4. TO_DATE('01/01/2019','DD/MM/YYYY')+level,
    5. 'A'||MOD(LEVEL,5)
    6. FROMdual
    7. CONNECTBYLEVEL<=100;
    8. COMMIT;
    TRUNCATE TABLE src;   insert into src(id, creation_date, grp) SELECT level,  TO_DATE('01/01/2019', 'DD/MM/YYYY') + level,   'A' || MOD(LEVEL, 5)  FROM dual CONNECT BY LEVEL <= 100;   COMMIT;
    And If I execute my query today, I'd like to have the following sample output:
    1. GRP3_WK_AGO3_WK_AGO1_WK_AGOTHIS_WEEK
    2. A02121
    3. A12112
    4. A21212
    5. A31211
    6. A41121
    GRP 3_WK_AGO 3_WK_AGO 1_WK_AGO THIS_WEEK A0 2 1 2 1 A1 2 1 1 2 A2 1 2 1 2 A3 1 2 1 1 A4 1 1 2 1
    Also, do you know why my PIVOT doesn't work? I don't mind using GROUP CASE but I was thinking this is a job for PIVOT. But I can't make it work Have a nice evening anyway 

    Post the query that isn't working.  (I know it can't be the query you posted in your original message; Jaramill told you how o fix that way back in reply #1.)

    Thanks for posting the new sample data.  That's much better for testing and discussion.

    Here's one way to use SELECT ... PIVOT:

    WITH    weeks_wanted    AS(    SELECT  LEVEL - 1   AS weeks_ago    ,       TRUNC ( SYSDATE - ((LEVEL - 1) * 7)                  , 'WW'                  )     AS start_date    FROM    dual    CONNECT BY  LEVEL <= 4  -- Chnage this to any number you want                            -- 4 means 3 weeks in the past plus current week),    data_to_pivot    AS(    SELECT  s.grp, w.weeks_ago    FROM    src           s    JOIN    weeks_wanted  w  ON   s.creation_date  >= w.start_date                             AND  s.creation_date  <  w.start_date + 7)SELECT    *FROM      data_to_pivotPIVOT     (    COUNT (*)          FOR  weeks_ago  IN (  3  AS wk_ago_3  -- Add weeks if needed                             ,  2  AS wk_ago_2                             ,  1  AS wk_ago_1                             ,  0  AS this_wk                             )          )ORDER BY  grp;

    Output (as requested):

    GRP        WK_AGO_3 WK_AGO_2 WK_AGO_1 THIS_WK---------- -------- -------- -------- -------A0                2        1        2       1A1                2        1        1       2A2                1        2        1       2A3                1        2        1       1A4                1        1        2       1

    If you want to change the number of weeks, change the "magic number" (currently 4) in the weeks_wanted sub-query, and add or remove the higher-numbered rows from the PIVOT .... IN clause.

    Do you really want to use 'WW' to define the weeks?  It will cause strange results around the end of each year.  I would use 'IW', or something based on 'IW'.

    user13117585
«1

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Apr 5, 2019 4:23PM

    FIRST Show us the "expected" output. 

    Also you should "to_char" your final column in your select statement part of your INSERT statement like below:

    DML

    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' || to_char(round(dbms_random.value (1, 9)))  --- applied to_char function to the round function which returns a number that you concatentate with a string thus the number should be a string as well.     from dual    connect by level <= 100;  

    Also you should "to_number" your expression in all your "WHEN" clauses like below:

    SQL

    select grp,       count(case when to_number(to_char(creation_date, 'WW')) = 1 then id end) as cnt_01,        count(case when to_number(to_char(creation_date, 'WW')) = 2 then id end) as cnt_02,       count(case when to_number(to_char(creation_date, 'WW')) = 3 then id end) as cnt_03,       count(case when to_number(to_char(creation_date, 'WW')) = 4 then id end) as cnt_04,       count(case when to_number(to_char(creation_date, 'WW')) = 5 then id end) as cnt_05,       count(case when to_number(to_char(creation_date, 'WW')) = 6 then id end) as cnt_06,       count(case when to_number(to_char(creation_date, 'WW')) = 7 then id end) as cnt_07,       count(case when to_number(to_char(creation_date, 'WW')) = 8 then id end) as cnt_08,       count(case when to_number(to_char(creation_date, 'WW')) = 9 then id end) as cnt_09,       count(case when to_number(to_char(creation_date, 'WW')) = 10 then id end) as cnt_10,       count(case when to_number(to_char(creation_date, 'WW')) = 11 then id end) as cnt_11,       count(case when to_number(to_char(creation_date, 'WW')) = 12 then id end) as cnt_12,       count(case when to_number(to_char(creation_date, 'WW')) = 13 then id end) as cnt_13,       count(case when to_number(to_char(creation_date, 'WW')) = 14 then id end) as cnt_14  from src group by grp;
  • user13117585
    user13117585 Member Posts: 640 Bronze Badge
    edited Apr 5, 2019 2:16PM

    For sure, I took some shortcuts in my sample scenario. I'm sorry for that.

    Me, I'm more concerned about the why, in the first query(with pivot), I don't have to proper sums/totals.

    In the second query I wrote, I'd like to know if we could have only the last 10 weeks in results...

    And, in the end, what i'm interested in is to have the results for all the grp for the last 10 days and if possible have the title of the column reflecting the week number. If it's not possible, maybe we could put number from -10 to -1 in title? Or something like that?

    Thank you for your answer anyway !

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Apr 5, 2019 2:18PM

    You still have not shown us the "expected" output.  What results/values are you expecting??

  • user13117585
    user13117585 Member Posts: 640 Bronze Badge
    edited Apr 5, 2019 2:24PM

    Basically, the resut of the second query is fine... Except that is that few columns I don't need. But, if it can help you, what I'd like is to have this:

    GRP  CNT_04  CNT_05  CNT_06  CNT_07  CNT_08  CNT_09  CNT_10  CNT_11  CNT_12  CNT_13  CNT_14AA        0      0      0      0      0      0      0      0      0      0      0A4        2      1      0      1      1      1      2      1      0      0      0A6        0      2      1      0      1      0      0      0      1      0      1A3        0      4      1      1      1      1      0      1      1      3      0A1        0      0      2      0      0      0      0      2      1      0      0A9        0      1      1      0      0      0      2      1      0      0      0A5        1      1      0      1      0      1      1      1      0      0      1A8        0      1      0      2      0      1      1      1      3      1      2A7        0      0      0      1      1      1      0      0      3      1      1A2        0      2      0      2      0      0      0      0      0      1      2

    And if I execute the same query in one week, the colum names should be different (from 05 to 15). IS it a little more clear now?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Apr 5, 2019 2:30PM

    Hi,

    Thanks for posting the CREATE TABLE and INSERT statements.  You should use anything random in the sample data; you want a consistent set of sample data so everybody that tries to help you can use the same data every time they try anything.

    Simplify the problem.  for example, instead of displaying 14 weeks, post a question that only has 2 or 3 weeks.  Just mention that you really need 14 weeks, so people will post solutions that can easily be adapted.

    In the 2nd query you have

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

    I'll bet the last 13 was supposed to be 14.

  • user13117585
    user13117585 Member Posts: 640 Bronze Badge
    edited Apr 5, 2019 2:38PM

    Ohh yes! Sorry! SHould be 14 in last CASE.

    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') = 14 THEN id END) AS cnt_14  FROM srcGROUP BY grp

    Actually, I put 14 CASE because we are on week 14. That is exactly my problem. How to have those results rolling? It's not very easy to explain and I'm sure it's even worse to understand

    If my question is not clear enough, I'll post another sample scenario without DMBS_RANDOM.

    Thank you guys for the answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Apr 5, 2019 2:58PM

    Okay great.  First you should use TO_NUMBER when comparing your WHEN expressions to be safe

    SQL

    set linesize 320column grp format a3select grp,       count(case when to_number(to_char(creation_date, 'WW')) = 1 then id end) as cnt_01,        count(case when to_number(to_char(creation_date, 'WW')) = 2 then id end) as cnt_02,       count(case when to_number(to_char(creation_date, 'WW')) = 3 then id end) as cnt_03,       count(case when to_number(to_char(creation_date, 'WW')) = 4 then id end) as cnt_04,       count(case when to_number(to_char(creation_date, 'WW')) = 5 then id end) as cnt_05,       count(case when to_number(to_char(creation_date, 'WW')) = 6 then id end) as cnt_06,       count(case when to_number(to_char(creation_date, 'WW')) = 7 then id end) as cnt_07,       count(case when to_number(to_char(creation_date, 'WW')) = 8 then id end) as cnt_08,       count(case when to_number(to_char(creation_date, 'WW')) = 9 then id end) as cnt_09,       count(case when to_number(to_char(creation_date, 'WW')) = 10 then id end) as cnt_10,       count(case when to_number(to_char(creation_date, 'WW')) = 11 then id end) as cnt_11,       count(case when to_number(to_char(creation_date, 'WW')) = 12 then id end) as cnt_12,       count(case when to_number(to_char(creation_date, 'WW')) = 13 then id end) as cnt_13,       count(case when to_number(to_char(creation_date, 'WW')) = 14 then id end) as cnt_14  from src group by grp order by grp asc;

    OUTPUT

    Time Start: 2019-04-05 14:57:52GRP     CNT_01     CNT_02     CNT_03     CNT_04     CNT_05     CNT_06     CNT_07     CNT_08     CNT_09     CNT_10     CNT_11     CNT_12     CNT_13     CNT_14--- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------A1           0          0          0          0          1          0          0          1          1          0          0          1          0          0A2           2          1          1          2          0          1          0          2          2          0          2          0          0          1A3           2          4          2          1          1          0          1          2          1          1          0          0          0          2A4           1          2          0          2          4          1          0          0          1          1          0          0          2          0A5           1          1          1          1          1          0          2          0          2          1          2          1          1          0A6           0          0          1          3          0          0          0          1          2          1          0          1          0          0A7           0          2          0          1          1          2          0          0          1          1          1          1          1          1A8           2          0          2          1          0          0          1          2          0          1          0          0          1          1A9           1          0          1          0          0          0          1          0          0          0          1          1          0          09 rows selected.Time End: 2019-04-05 14:57:52Elapsed Time for Script Execution: 851 msecs
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Apr 5, 2019 3:08PM

    Hi,

    user13117585 wrote:Ohh yes! Sorry! SHould be 14 in last CASE.
    1. SELECTgrp,
    2. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=1THENidEND)AScnt_01,
    3. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=2THENidEND)AScnt_02,
    4. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=3THENidEND)AScnt_03,
    5. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=4THENidEND)AScnt_04,
    6. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=5THENidEND)AScnt_05,
    7. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=6THENidEND)AScnt_06,
    8. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=7THENidEND)AScnt_07,
    9. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=8THENidEND)AScnt_08,
    10. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=9THENidEND)AScnt_09,
    11. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=10THENidEND)AScnt_10,
    12. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=11THENidEND)AScnt_11,
    13. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=12THENidEND)AScnt_12,
    14. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=13THENidEND)AScnt_13,
    15. COUNT(CASEWHENTO_CHAR(creation_date,'WW')=14THENidEND)AScnt_14
    16. FROMsrc
    17. GROUPBYgrp
    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') = 14 THEN id END) AS cnt_14  FROM src GROUP BY grp

    The number of columns in a result set, and their names, are fixed when the query is compiled, before it fetches any data.  If you want the number of columns, or their names, to depend on any data fetched (even SYSDATE), then you need Dynamic SQL.

    One alternative is to have a fixed number of columns, with fixed names, but have the stuff that depends on what's in the table be in the result set itself.  For example, if you want a column to be called "CNT_14" if they query is run in week 14, but you want the same column to be called "CNT_15" if you run the query in week 15, then you can add a row to your result set that has 'CNT_14' or 'CNT_15', and format the output so that row looks like a header.

    Actually, I put 14 CASE because we are on week 14. That is exactly my problem. How to have those results rolling?  It's not very easy to explain and I'm sure it's even worse to understand If my question is not clear enough, I'll post another sample scenario without DMBS_RANDOM.

    Yes, post another example with fixed data, and include only a few weeks, say weeks 12 through 15.  Then say something like "If I run this query on between April 2 and April 8, 2019, then I want to display weeks 12-14 like this... but if I run the query between April 9 and April 15, 2019, then I want weeks 13-15 like this ... because ..."

  • user13117585
    user13117585 Member Posts: 640 Bronze Badge
    edited Apr 5, 2019 3:24PM

    Ok,

    Thank you Frank Kulash for your answer. I was afraid of that actually. But since I don't really want to use dynamic SQL, I'd stick to the solution you suggest and put a name for each column. So, instead of week number, I think I'll try to have something like

    THREE_WEEKS_AGO, TWO_WEEKS_AGO, LAST_WEEK.

    Now, if I change my sample data like this:

    TRUNCATE TABLE src;insert into src(id, creation_date, grp)SELECT level,      TO_DATE('01/01/2019', 'DD/MM/YYYY') + level,       'A' || MOD(LEVEL, 5)  FROM dualCONNECT BY LEVEL <= 100;COMMIT;

    And If I execute my query today, I'd like to have the following sample output:

    GRP 3_WK_AGO  3_WK_AGO  1_WK_AGO  THIS_WEEKA0        2        1        2          1A1        2        1        1          2A2        1        2        1          2A3        1        2        1          1A4        1        1        2          1

    Also, do you know why my PIVOT doesn't work? I don't mind using GROUP CASE but I was thinking this is a job for PIVOT. But I can't make it work

    Have a nice evening anyway

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Apr 5, 2019 4:27PM Accepted Answer

    Hi,

    user13117585 wrote:Ok,Thank you Frank Kulash for your answer. I was afraid of that actually. But since I don't really want to use dynamic SQL,

    Good idea!    Dynamic SQL is often not worth the trouble.

     I'd stick to the solution you suggest and put a name for each column. So, instead of week number, I think I'll try to have something likeTHREE_WEEKS_AGO, TWO_WEEKS_AGO, LAST_WEEK.Now, if I change my sample data like this:
    1. TRUNCATETABLEsrc;
    2. insertintosrc(id,creation_date,grp)
    3. SELECTlevel,
    4. TO_DATE('01/01/2019','DD/MM/YYYY')+level,
    5. 'A'||MOD(LEVEL,5)
    6. FROMdual
    7. CONNECTBYLEVEL<=100;
    8. COMMIT;
    TRUNCATE TABLE src;   insert into src(id, creation_date, grp) SELECT level,  TO_DATE('01/01/2019', 'DD/MM/YYYY') + level,   'A' || MOD(LEVEL, 5)  FROM dual CONNECT BY LEVEL <= 100;   COMMIT;
    And If I execute my query today, I'd like to have the following sample output:
    1. GRP3_WK_AGO3_WK_AGO1_WK_AGOTHIS_WEEK
    2. A02121
    3. A12112
    4. A21212
    5. A31211
    6. A41121
    GRP 3_WK_AGO 3_WK_AGO 1_WK_AGO THIS_WEEK A0 2 1 2 1 A1 2 1 1 2 A2 1 2 1 2 A3 1 2 1 1 A4 1 1 2 1
    Also, do you know why my PIVOT doesn't work? I don't mind using GROUP CASE but I was thinking this is a job for PIVOT. But I can't make it work Have a nice evening anyway 

    Post the query that isn't working.  (I know it can't be the query you posted in your original message; Jaramill told you how o fix that way back in reply #1.)

    Thanks for posting the new sample data.  That's much better for testing and discussion.

    Here's one way to use SELECT ... PIVOT:

    WITH    weeks_wanted    AS(    SELECT  LEVEL - 1   AS weeks_ago    ,       TRUNC ( SYSDATE - ((LEVEL - 1) * 7)                  , 'WW'                  )     AS start_date    FROM    dual    CONNECT BY  LEVEL <= 4  -- Chnage this to any number you want                            -- 4 means 3 weeks in the past plus current week),    data_to_pivot    AS(    SELECT  s.grp, w.weeks_ago    FROM    src           s    JOIN    weeks_wanted  w  ON   s.creation_date  >= w.start_date                             AND  s.creation_date  <  w.start_date + 7)SELECT    *FROM      data_to_pivotPIVOT     (    COUNT (*)          FOR  weeks_ago  IN (  3  AS wk_ago_3  -- Add weeks if needed                             ,  2  AS wk_ago_2                             ,  1  AS wk_ago_1                             ,  0  AS this_wk                             )          )ORDER BY  grp;

    Output (as requested):

    GRP        WK_AGO_3 WK_AGO_2 WK_AGO_1 THIS_WK---------- -------- -------- -------- -------A0                2        1        2       1A1                2        1        1       2A2                1        2        1       2A3                1        2        1       1A4                1        1        2       1

    If you want to change the number of weeks, change the "magic number" (currently 4) in the weeks_wanted sub-query, and add or remove the higher-numbered rows from the PIVOT .... IN clause.

    Do you really want to use 'WW' to define the weeks?  It will cause strange results around the end of each year.  I would use 'IW', or something based on 'IW'.

    user13117585