Forum Stats

  • 3,853,198 Users
  • 2,264,190 Discussions
  • 7,905,286 Comments

Discussions

Need some assistance with PIVOT function - pivoting salesdata for each month

TheBlakester
TheBlakester Member Posts: 1
edited Dec 4, 2014 2:02AM in SQL & PL/SQL

Hi all,


I could use some help from the PL/SQL gurus here, with pivoting data in Oracle. I have data that comes in each month that looks like this:

Create table test_incoming_data (

Salesrep_Id number,

Product_id number,

Sales_date varchar2 (8),

New_count number,

Refill_count number);

The Sales_Date column comes in the format YYYYMMDD, such as 20140731. What I need to do is pivot the data out for each month on Salesrep_Id and Product_Id, so that the final table has one row for each Salesrep_Id and Product_id, and then it pivots out the data into separate columns for each month. It also needs to separate out the New vs. Refill. The most recent month of data will go into column_A, the next most recent into Column_B, etc. This data goes back 24 months, so we will have columns A through X. So for example, let’s say the current dataset is from November 2014 data going back. So Month_A would be for November 2014, Month_B would be October 2014, Month_C for September, etc.

So in the end, the output needs to look like this:

Salesrep_Id,

Product_id,

Month_A_New_Count (this is the sum of the most recent month of the New_count value),

Month_A_Refill_Count (this is the sum of the most recent month of the refill_count value),

Month_B_New_Count,

Month_B_Refill_Count,

Month_C_New_Count,

Month_C_Refill_Count,

   ... Etc….. all the way to

Month_X_New_Count,

Month_X_Refill_Count

Please let me know if more details are needed, and/or some test data? I appreciate any assistance on this!!

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Dec 3, 2014 10:25PM

    Hi,

    TheBlakester wrote:
    
    Hi all,
    
    I could use some help from the PL/SQL gurus here, with pivoting data in Oracle.

    Why PL/SQL?  Pivoting is simpler and more efficient in SQL.

    I have data that comes in each month that looks like this:
    
    Create table test_incoming_data (
    Salesrep_Id number,
    Product_id number,
    Sales_date varchar2 (8),
    New_count number,
    Refill_count number);
    
    The Sales_Date column comes in the format YYYYMMDD, such as 20140731.
    
    

    That's not a very good idea.  Information about dates belong in DATE columns, not VARCHAR2s.  Storing dates in any datatype other than a DATE (or TIMESTAMP) is simply asking for trouble.  Sometimes you get what you ask for.

    What I need to do is pivot the data out for each month on Salesrep_Id and Product_Id, so that the final table has one row for each Salesrep_Id and Product_id, and then it pivots out the data into separate columns for each month. It also needs to separate out the New vs. Refill. The most recent month of data will go into column_A, the next most recent into Column_B, etc. This data goes back 24 months, so we will have columns A through X. So for example, let’s say the current dataset is from November 2014 data going back. So Month_A would be for November 2014, Month_B would be October 2014, Month_C for September, etc.
    
    
    

    You can use the MAX function to find the last month in the data set.  You can use MONTHS_BETWEEN to find out how many months a given date is before that point.  (Let's call that number m.)  Finally, you can use SELECT ... PIVOT to put the data with m between 1 and 24 into columns 1 through 24.

    So in the end, the output needs to look like this:
    
    Salesrep_Id,
    Product_id,
    Month_A_New_Count (this is the sum of the most recent month of the New_count value),
    Month_A_Refill_Count (this is the sum of the most recent month of the refill_count value),
    Month_B_New_Count,
    Month_B_Refill_Count,
    Month_C_New_Count,
    Month_C_Refill_Count,
       ... Etc….. all the way to
    Month_X_New_Count,
    Month_X_Refill_Count
    
    Please let me know if more details are needed, and/or some test data? I appreciate any assistance on this!!
    
    
    

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Simplify the problem, if possible.  For example, instead of getting separate columns for 24 months, post a problem with only 4 months.  (Just make it clear that you'll really need 24.)

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: 

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown

    Something to play with

    with

    test_incoming_data as

    (select trunc(dbms_random.value(1,6)) Salesrep_Id,

            trunc(dbms_random.value(100,111)) Product_id,

            sysdate - trunc(dbms_random.value(5,150)) Sales_date,

            trunc(dbms_random.value(10,101)) New_count,

            trunc(dbms_random.value(10,51)) Refill_count

      from dual

    connect by level <= 1000

    )

    select salesrep_id,product_id,

           nov_new,nov_ref,oct_new,oct_ref,sep_new,sep_ref,aug_new,aug_ref,jul_new,jul_ref

      from (select salesrep_id,product_id,to_char(sales_date,'mm') mnth,sum(new_count) new_count,sum(refill_count) refill_count

              from test_incoming_data

             group by salesrep_id,product_id,to_char(sales_date,'mm')

           )

    pivot (sum(new_count) new,max(refill_count) ref for mnth in ('11' as nov,

                                                                  '10' as oct,

                                                                  '09' as sep,

                                                                  '08' as aug,

                                                                  '07' as jul

                                                                 )

           )

    order by salesrep_id,product_id

    <td headers="OCT_NEW" style="padding: 3px 9px; border-top-width: 1px; border-top-color: #ffffff; border-top-style: solid; b
    SALESREP_IDPRODUCT_IDNOV_NEWNOV_REFOCT_NEWOCT_REFSEP_NEWSEP_REFAUG_NEWAUG_REFJUL_NEWJUL_REF
    11002747127107384164310134292108
    11018513117134105134123130--
    11021055615283276163397147--
    110326611198461801202549515487
    11041253512476161118214516039
    110521676176126379118322013874
    1106791173551139382666722767
    11072069037719277867677224128
    1108103872899437017199958661
    1109251825616025714617455243100
    1110239821611121601161817911877
    2100261150--423153256116309179
    21011607121614269107379109215115
    2102177442541261451603531449272
    210351637830821015217641319115674
    2104231115138551291688887115118
    21051707715212038222828711816890
    2106293181228126208155339158208113
    21072271811608319498328166252157
    210810144
This discussion has been closed.