11 Replies Latest reply on Feb 17, 2019 11:30 PM by fac586

    Dynamic Pivot

    San

      Hi All,

       

      I have a requirement to display month wise count for the branch.

       

      Branch name (input parameter) and  month (input parameter)

      Ex:

      Branch - UK and month - Sep-18 as input parameter , i want to display my report as below

       

      UK                Sep17 Oct 17 Nov-17 Dec-17 Jan-18 Feb-18 mar-18 ................ Sep-18

      No of sales    10      20         30        40        100       120      140    ....................200

       

      Below are query to get the one year date based on the input parameter.

       

      SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS

      FROM DUAL

      CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11))

       

      I need to convert this as a column and display in my report.

       

      I cant hard coded the month values in PIVOT function , i tried with below query and its not working .

       

      WITH TAB1 AS(

      SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS

      FROM DUAL

      CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11)))

      select MONTHS from TAB1

      pivot (

        count(*) for MONTHS in(SELECT listagg(MONTHS,',') within group (order by MONTHS) FROM TAB1)

       

      Regards,

      San

        • 1. Re: Dynamic Pivot
          Ahmed Haroon

          Please have a table structure and insert statement ( Create Table and Insert Into .... ) for your sample data to help seniors who can help you.

          • 2. Re: Dynamic Pivot
            Gaz in Oz

            As Ahmed says, you should post create table and insert into..

             

            You can simplify the months generation to something like this:

            var p2_date varchar2(6) = 'SEP-18'
            
            WITH tab1 AS (
              SELECT ADD_MONTHS(TO_DATE(:P2_DATE, 'MON-YY'), 1 - level) months
              FROM   dual
              CONNECT BY level <= 13
            )
            select TO_CHAR(months, 'MON-YY') months from tab1
            /
            
            • 3. Re: Dynamic Pivot
              San

              I dont have any table structure for this . All i need is get the input parameter (branch and month) and return the result set as

               

              Branch name and one year month wise split as a row.

               

              like

              If you take SEP-18

              UK SEP17 OCT17 NOV17 DEC17 JAN18 FEB18 MAR18 APR18 MAY ...... SEP18

               

              Based on the branch UK and Month i will pass the value to source table and calculate the result and display in the next row.

              • 4. Re: Dynamic Pivot
                AndrewSayer

                san wrote:

                 

                Hi All,

                 

                I have a requirement to display month wise count for the branch.

                 

                Branch name (input parameter) and month (input parameter)

                Ex:

                Branch - UK and month - Sep-18 as input parameter , i want to display my report as below

                 

                UK Sep17 Oct 17 Nov-17 Dec-17 Jan-18 Feb-18 mar-18 ................ Sep-18

                No of sales 10 20 30 40 100 120 140 ....................200

                 

                Below are query to get the one year date based on the input parameter.

                 

                SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS

                FROM DUAL

                CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11))

                 

                I need to convert this as a column and display in my report.

                 

                I cant hard coded the month values in PIVOT function , i tried with below query and its not working .

                 

                WITH TAB1 AS(

                SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY')-365, LEVEL -1), 'MON-YY') MONTHS

                FROM DUAL

                CONNECT BY LEVEL <= MONTHS_BETWEEN(ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),2), ADD_MONTHS(TO_DATE(:P2_DATE,'MON-YY'),-11)))

                select MONTHS from TAB1

                pivot (

                count(*) for MONTHS in(SELECT listagg(MONTHS,',') within group (order by MONTHS) FROM TAB1)

                 

                Regards,

                San

                Dynamic pivoting is best done by a reporting tool. Google how to do it for the specific reporting tool you are using.

                 

                You could mess around with dynamic SQL but you'd need to get everything to expect different columns depending on when it's run.

                • 5. Re: Dynamic Pivot
                  San

                  I am using Oracle application express which again needs sql query.

                  • 6. Re: Dynamic Pivot
                    AndrewSayer

                    san wrote:

                     

                    I am using Oracle application express which again needs sql query.

                    Apex Interactive reports can handle the pivoting for you. https://docs.oracle.com/en/database/oracle/application-express/18.2/aeeug/managing-pivot-reports.html#GUID-B71B947D-FC08-466C-9739-DFFE7981F4E8 you can save the pivot report as the primary report too.

                    • 7. Re: Dynamic Pivot
                      San

                      I have a restrictions to use IR in apex. Let me rewrite my question

                       

                      Now i have created a table which  holds month wise sales data for the branch.

                       

                      Table name : Branch

                      Branch Month Sales

                      UK       JAN-18 10

                      UK       FEB-18  15

                      UK      MAR-18  20

                      UK      APR-18    10

                       

                      Like this i have  stored data for all the branches with month and year wise. Now if my user select a branch and select a month of JAN-18

                      i need to return a result set as

                       

                      UK      JAN-17 feb-17 MAR-17 APR-17 ........ JAN-18

                      Sales   15         20        10          15      . .........  10

                       

                      Based on the month input i need to give last one year data in the pivot format.

                      • 8. Re: Dynamic Pivot
                        Mustafa KALAYCI

                        Hello,

                         

                        what you ask is not a job for database! it is a job for reporting tool. Can you do it in Oracle? Yes! Should you? No!

                        It will be unnecessary workload for database. you can try dynamic sql to generate your dynamic columns or also there is an ODCI if I remember correctly lets you generate dynamic columns but I believe most appropriate solution is this:

                        https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538416700346392532

                        and this is come from a simple google search

                         

                        edit: Also thanks to Stew Ashton, base solution belongs to him.

                        • 9. Re: Dynamic Pivot
                          Ahmed Haroon

                          this can be done in 2 steps, please see and try to understand ( i have just tested only first step to prepare statement for 2nd step )

                           

                          with Branch (Branch,Months,Sales) as
                            (Select 'UK','JAN-16',10 from dual union all
                             Select 'UK','FEB-16',20 from dual union all
                             Select 'UK','MAR-16',20 from dual union alL
                             Select 'UK','APR-16',20 from dual union all
                             Select 'UK','MAR-17',10 from dual union all
                             Select 'UK','APR-17',20 from dual union all
                             Select 'UK','MAY-17',15 from dual union all
                             Select 'UK','JUN-17',10 from dual union all
                             Select 'UK','JUL-17',20 from dual union all
                             Select 'UK','AUG-17',30 from dual union all
                             Select 'UK','SEP-17',40 from dual union all
                             Select 'UK','OCT-17',50 from dual union all
                             Select 'UK','NOV-17',60 from dual union all
                             Select 'UK','DEC-17',70 from dual union all
                             Select 'UK','JAN-18',80 from dual union all
                             Select 'UK','FEB-18',90 from dual union all
                             Select 'UK','MAR-18',10 from dual 
                          ),
                          sel(months) as 
                            (select to_date(months,'MON-YY') 
                               from branch 
                              where to_date(months,'MON-YY') <= to_date('JAN-18','MON-YY')
                              order by 1 desc
                            ),
                            prep(in_list) as (
                            select listagg(chr(39)||to_char(months, 'MON-YY')||chr(39), ', ')
                                   within group (order by months)
                              from sel
                             where rownum<= 12
                          )
                          select 'select *'   || chr(10) ||
                                 'from   (Select * from Branch order by to_date(months,''MON-YY'') )' || chr(10) ||
                                 'pivot(sum(sales) for months in (' || chr(10) ||
                                 '                   ' || in_list         || chr(10) ||
                                 '               )'                       || chr(10) ||
                                 '     )'
                                 as sql_str
                          from   prep
                          

                           

                          when you will run this (above) will generate SQL statement like following to run and get final result / output:

                          select * from (Select * from Branch order by months) pivot(sum(sales) for months in ( 'APR-16', 'MAR-17', 'APR-17', 'MAY-17', 'JUN-17', 'JUL-17', 'AUG-17', 'SEP-17', 'OCT-17', 'NOV-17', 'DEC-17', 'JAN-18' ) )

                          hope this will help

                           

                          EDIT: I have modified and update above SQL statement after getting help from Manik  thanks to him again for his valuable time

                          • 10. Re: Dynamic Pivot
                            AndrewSayer

                            san wrote:

                             

                            I have a restrictions to use IR in apex. Let me rewrite my question

                             

                            Now i have created a table which holds month wise sales data for the branch.

                             

                            Table name : Branch

                            Branch Month Sales

                            UK JAN-18 10

                            UK FEB-18 15

                            UK MAR-18 20

                            UK APR-18 10

                             

                            Like this i have stored data for all the branches with month and year wise. Now if my user select a branch and select a month of JAN-18

                            i need to return a result set as

                             

                            UK JAN-17 feb-17 MAR-17 APR-17 ........ JAN-18

                            Sales 15 20 10 15 . ......... 10

                             

                            Based on the month input i need to give last one year data in the pivot format.

                            Do you mean you have to use IR? Good, that's all you need. Do you mean you have to not use IR? Why not? It solves the problem you want to solve...

                             

                            Anyway, I've put together an easy demo https://apex.oracle.com/pls/apex/f?p=30017:1

                            • 11. Re: Dynamic Pivot
                              fac586

                              san wrote:

                               

                              I am using Oracle application express which again needs sql query.

                              Then maybe the question would have been more appropriate to the Oracle Application Express (APEX) forum?

                               

                              Here are two approaches for dynamic matrix reporting using classic reports: