Forum Stats

  • 3,855,365 Users
  • 2,264,500 Discussions
  • 7,905,979 Comments

Discussions

PIVOT vs GROUP BY

2»

Answers

  • user13117585
    user13117585 Member Posts: 662 Bronze Badge
    edited Apr 5, 2019 4:29PM

    Thank you Frank! I will test this solution.

    About your message, about me using the WW format. This is a really simplified version of my problem. Basically, I would need begin and end week. But, if it works with the week number, I can manage the rest. I don't like to post a very complex scenario while I'm a little bit lost in a very specific case. Like in here.

    SO, I will check this suggestion, try to understand, see how it behaves where we have some groups with nothing for one complete week, etc etc.

    Thank you again !

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited Apr 5, 2019 4:48PM

    Hi,

    user13117585 wrote:...SO, I will check this suggestion, try to understand, see how it behaves where we have some groups with nothing for one complete week, etc etc....

    Yes, you should test things like that.  Include special cases when you post your sample data.  For example, after populating the src table as you did in reply #9, add 1 one more row:

    INSERT INTO src (id,   creation_date,      grp)           VALUES (1001, DATE '2019-04-01', 'Z0');

    Sometimes, handling the special cases requires a completely different approach.  People can waste a lot of time writing something for the simpler case, only to find that you can't use on your real data.

    If you want to include grps from the table that have nothing for ANY week in the window, then use an outer join in the sub-query data_to_pivot.

    user13117585
  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    edited Apr 5, 2019 6:08PM

    The problem with your PIVOT query, as pointed out in Reply 1, is that TO_CHAR(<date>, 'WW') returns strings like '01'. In the PIVOT operator, when the IN list is evaluated, the numbers 1, 2, etc. are converted to the strings '1', '2' etc. - which are not equal to '01', '02' etc.  The solution is also offered in Reply 1.

    However, you are making your PIVOT query too complicated. You must still use proper data types (and proper, explicit conversions as needed), but you don't need to aggregate in the subquery; pivoting IS a form of aggregation. Your PIVOT attempt should instead look like this:

    WITH get_counts AS ( SELECT to_number(TO_CHAR(creation_date, 'WW')) AS week_number,        grp  FROM src      --  No GROUP BY, no aggregate functions in this subquery
    ) SELECT *   FROM get_counts c PIVOT (   count(*)   FOR week_number IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) ) ORDER BY grp ;

    This only improves the static PIVOT query - it doesn't do any of the dynamic stuff you need. But you may (and should!) use the same ideas in whatever dynamic (or modified static) solution you end up with.

    user13117585
  • user13117585
    user13117585 Member Posts: 662 Bronze Badge
    edited Apr 6, 2019 11:57AM

    Thank you very much everyone. I understand my problems and also understand how to make the query I need.

    Regards,