1 2 Previous Next 19 Replies Latest reply on Oct 15, 2019 12:24 PM by Ram venu

    Dynamic Pivoting

    Ram venu

      HI

       

      I have data like below

       

      "USER"                     "OFFICE_ID"                   "OFFICE_NAME"                   " WEEKDAY"                     "(SUBSTR(TO_CHAR(WEEKDAY,'DAY'),1,3))""        REQ_REC_30_DAY"              "REQ_REC_LESS_6MO"            "REQ_REC_MORE_6MO"            "REQ_REC_WINDOW"             

      "user1"                   "HAR"                              CHICAGO                           "04-FEB-19"                                                 MON                                                                 1                                                           NULL                                         1                                              NULL                           

      "user1"                   "HAR"                              CHICAGO                           "05-FEB-19"                                                 TUE                                                                NULL                                                        1                                              NULL                                          NULL                       

      "user2"                   "HAR"                             CHICAGO                            "04-FEB-19"                                                 MON                                                                2                                                             1                                               NULL                                        NULL                                         

      "user2"                   "HAR"                             CHICAGO                            "05-FEB-19"                                                 TUE                                                                 3                                                             8                                                  1                                                 6

       

       

      I want output like below

       

                                                                                           "04-FEB-19"               "05-FEB-19"    

                                                                                               MON                         TUE

         30 Days              ("REQ_REC_30_DAY" )                       1                               null  

        < 6 MONTHS     ("REQ_REC_LESS_6MO" )                   null                            1

        >6 MONTHS      ("REQ_REC_MORE_6MO" )                 1                               null

        window             ( "REQ_REC_WINDOW")                       null                            null

       

      please help how can I achieve

       

      Thanks

      Ram

        • 1. Re: Dynamic Pivoting
          Mike Kutz

          It is usually better to do this type of Dynamic Pivoting within the Display Tier.

          (eg BI Publisher, APEX Report, PowerBI, etc.)

           

          Columns names must be known at compile time.

          • 2. Re: Dynamic Pivoting
            jaramill

            Ram venu wrote:

             

            please help how can I achieve

             

            Thanks

            Ram

            Besides what Mike wrote, what query have YOU written that you need help with?

             

            Please post DDL and DML and SQL to help along with DB version.  Read the link on --> Re: 2. How do I ask a question on the forums?

            Answer questions #5 through #9

            • 3. Re: Dynamic Pivoting
              Ram venu

              I am trying to use Pivot to convert date into columns , but issue is that the column values are coming from column , but Pivot accepts only values

               

              how can I enter column name instead of values in Pivot for IN clause ?

              • 4. Re: Dynamic Pivoting
                Mike Kutz

                Ram venu wrote:

                 

                I am trying to use Pivot to convert date into columns , but issue is that the column values are coming from column , but Pivot accepts only values

                 

                how can I enter column name instead of values in Pivot for IN clause ?

                Hand name them.

                 

                with data as (
                    select e.sal, e.job, e.deptno
                    from emp e
                      join dept d on e.deptno=d.deptno
                )
                select * from data
                pivot (
                  count(*) as N
                  ,sum(sal) as Total_Salary
                  for deptno in ( 10 as NEW_YORK, 20 as DALLAS, 30 AS CHICAGO, 40 AS BOSTON)
                )
                

                 

                 

                My $0.02

                 

                MK

                • 5. Re: Dynamic Pivoting
                  Ram venu

                  for deptno in ( 10 as NEW_YORK, 20 as DALLAS, 30 AS CHICAGO, 40 AS BOSTON) 

                   

                  I have to use column_name from query to populate values in IN clause

                   

                   

                  for deptno in ( date_col) 

                  • 6. Re: Dynamic Pivoting
                    Frank Kulash

                    Hi,

                     

                    See

                     

                    As others have said, this is best done in your front end.  Exactly how depends on what front end you are using.  In SQL*Plus, for example, you can use substitution variables or the @ command.

                     

                    As always, if you want specific help, post a little sample data (CREATE TABLE and INSERT statements), the exact results you want from that data, and an explanation of how you get those results from that data.

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

                    See the forum FAQ: Re: 2. How do I ask a question on the forums?

                    • 7. Re: Dynamic Pivoting
                      Ram venu

                      INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID1','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,1,NULL,NULL,'TUE');

                      INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),NULL,2,1,NULL,'MON');

                      INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,NULL,NULL,NULL,'TUE');

                      Insert into EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) values ('USERID1','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),1,null,1,null,'MON');

                       

                       

                       

                      DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                       

                      from above I want output like

                       

                                                                                                           "04-FEB-19"               "05-FEB-19"    

                                                                                                               MON                         TUE

                         30 Days              ("REQ_REC_30_DAY" )                       1                               null  

                        < 6 MONTHS     ("REQ_REC_LESS_6MO" )                   null                            1

                        >6 MONTHS      ("REQ_REC_MORE_6MO" )                 1                               null

                        window             ( "REQ_REC_WINDOW")                       null                            null

                       

                         30 Days ,  < 6 MONTHS,  >6 MONTHS ,  window  are custom columns

                       

                      The weekday column will split into columns

                       

                      please let me know if you need any information

                      • 8. Re: Dynamic Pivoting
                        Mike Kutz

                        Ram venu wrote:

                         

                        please let me know if you need any information

                         

                        Why are you using 2-digit years?

                        Did Y2K not teach you anything?  Are you aware that the RR feature is a "band-aid fix"?  You do know that the result is non-deterministic and will change soon.  Right?

                        • 9. Re: Dynamic Pivoting
                          jaramill

                          Yes...you forgot to give us the DDL (table definition of the "export_table)

                          Also stop using the format mask of RR.   That was for the Y2K bug back in the year 2k (2000).  Use all 4 digits.

                          • 10. Re: Dynamic Pivoting
                            Mike Kutz

                            Ram venu wrote:

                             

                            INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID1','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,1,NULL,NULL,'TUE');

                            INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),NULL,2,1,NULL,'MON');

                            INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,NULL,NULL,NULL,'TUE');

                            Insert into EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) values ('USERID1','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),1,null,1,null,'MON');

                             

                             

                             

                            DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                             

                            from above I want output like

                             

                            "04-FEB-19" "05-FEB-19"

                            MON TUE

                            30 Days ("REQ_REC_30_DAY" ) 1 null

                            < 6 MONTHS ("REQ_REC_LESS_6MO" ) null 1

                            >6 MONTHS ("REQ_REC_MORE_6MO" ) 1 null

                            window ( "REQ_REC_WINDOW") null null

                             

                            30 Days , < 6 MONTHS, >6 MONTHS , window are custom columns

                             

                            The weekday column will split into columns

                             

                            please let me know if you need any information

                            Static SQL

                            Not going to happen within Static SQL.

                             

                            The resulting column name must be known at compile time.

                             

                            Perform the dynamic pivot within the Display Tier.

                             

                            Other Option:

                            use XML PIVOT.

                            But, then, you have to deal with translating the XML into something you can display.  (this has been done within an APEX Application)

                             

                            ie Perform the dynamic pivot within the Display Tier

                             

                            Dynamic SQL

                            The usage of Dynamic SQL is usually a Red Flag of a bad design somewhere.

                             

                            Again, I highly recommend that you move the Dynamic Pivoting to the Display Tier.

                             

                            Polymorphic Table Function

                            It might be possible to wrap the Dynamic SQL with a PTF, but the date range for the column names must be deduced by the static values (NO VARIABLES) given to the Function.

                             

                            ie Column Names must be known as compile time.

                             

                            Also, PFTs are an 18c feature.  Oracle-Base write up is here.

                             

                            MK

                            • 11. Re: Dynamic Pivoting
                              Frank Kulash

                              Hi, Ram,

                              Ram venu wrote:

                              ...

                              please let me know if you need any information

                              Just what I mentioned earlier:

                              1. An explanation of how you get the desired results from the given data.  (For example, are the numbers display sums?)
                              2. What display tier (i.e., front end) you are using
                              3. Your Oracle version
                              • 12. Re: Dynamic Pivoting
                                Ram venu

                                Hello Mike ,

                                 

                                It was old query written in long back in oracle reports.

                                • 13. Re: Dynamic Pivoting
                                  Ram venu
                                  1. An explanation of how you get the desired results from the given data.  (For example, are the numbers display sums?)

                                   

                                        we should not do sum here, query already returns values , we just have to transpose columns and values

                                   

                                        if you check my 1st post , we have WEEKDAY ,REQ_REC_30_DAY,REQ_REC_LESS_60,                                           REQ_REC_MORE_60 AND REQ_REC_WINDOW columns.

                                   

                                          weekday has dates and for each date there are values for REQ_REC_30_DAY,REQ_REC_LESS_60,                                           REQ_REC_MORE_60 AND REQ_REC_WINDOW ,

                                        we have to convert weekday dates into columns and other column values in cell same like pivot structure,

                                   

                                  example :

                                   

                                  weekday                   30_day                  less_60_day              more_60_day          window

                                  01-01-2019                    1                              2                                  3                        4

                                  01-02-2019                    5                              6                                  7                        8

                                   

                                  output will be :

                                                                              01-01-2019                 01-02-2019

                                  30_day                                       1                                5

                                  less_60_day                               2                                6

                                  more_60_day                             3                                7

                                  window                                       4                                8

                                   

                                         What display tier (i.e., front end) you are using

                                           Oracle Bi Publisher

                                   

                                    3. Your Oracle version

                                      DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                                  • 14. Re: Dynamic Pivoting
                                    AndrewSayer

                                    Mike Kutz wrote:

                                     

                                    Ram venu wrote:

                                     

                                    INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID1','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,1,NULL,NULL,'TUE');

                                    INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),NULL,2,1,NULL,'MON');

                                    INSERT INTO EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) VALUES ('USERID2','CHI','CHICAGO',to_date('05-FEB-19','DD-MON-RR'),NULL,NULL,NULL,NULL,'TUE');

                                    Insert into EXPORT_TABLE (USER_ID,OFFICE_ID,OFFICE_NAME,WEEKDAY,REQ_REC_30_DAY,REQ_REC_LESS_6MO,REQ_REC_MORE_6MO,REQ_REC_WINDOW,DY) values ('USERID1','CHI','CHICAGO',to_date('04-FEB-19','DD-MON-RR'),1,null,1,null,'MON');

                                     

                                     

                                     

                                    DB Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                                     

                                    from above I want output like

                                     

                                    "04-FEB-19" "05-FEB-19"

                                    MON TUE

                                    30 Days ("REQ_REC_30_DAY" ) 1 null

                                    < 6 MONTHS ("REQ_REC_LESS_6MO" ) null 1

                                    >6 MONTHS ("REQ_REC_MORE_6MO" ) 1 null

                                    window ( "REQ_REC_WINDOW") null null

                                     

                                    30 Days , < 6 MONTHS, >6 MONTHS , window are custom columns

                                     

                                    The weekday column will split into columns

                                     

                                    please let me know if you need any information

                                    Static SQL

                                    Not going to happen within Static SQL.

                                     

                                    The resulting column name must be known at compile time.

                                     

                                    Perform the dynamic pivot within the Display Tier.

                                     

                                    Other Option:

                                    use XML PIVOT.

                                    But, then, you have to deal with translating the XML into something you can display. (this has been done within an APEX Application)

                                     

                                    ie Perform the dynamic pivot within the Display Tier

                                     

                                    Dynamic SQL

                                    The usage of Dynamic SQL is usually a Red Flag of a bad design somewhere.

                                     

                                    Again, I highly recommend that you move the Dynamic Pivoting to the Display Tier.

                                     

                                    Polymorphic Table Function

                                    It might be possible to wrap the Dynamic SQL with a PTF, but the date range for the column names must be deduced by the static values (NO VARIABLES) given to the Function.

                                     

                                    ie Column Names must be known as compile time.

                                     

                                    Also, PFTs are an 18c feature. Oracle-Base write up is here.

                                     

                                    MK

                                    If you were doing this in APEX, like most reporting friendly front ends, you wouldnt use the pivot clause at all, you’d just return the detail to APEX and let it do all the data display shuffling: Monty Latiolais: Pivots Made Easy with APEX 5.0

                                    1 2 Previous Next