9 Replies Latest reply on Mar 13, 2019 2:57 PM by Tim D

    Pivot Dynamic Dates to Columns and Value Headers to Rows

    Tim D

      I have a table structure similar to this:

      Item    Item Descr     StartDate    Inventory    TotSupply   SchedRcpts  ProjOH 
      -------------------------------------------------------------------------------
      12345   Widget         3/8/2019     50           50          50          100
      12345   Widget         3/15/2019    25           25          25          50

      I need a query or queries that will result in getting the data in this format/layout (NOTE: dates are dynamic weeks ending Friday):

      Item    Item Descr     Value_Type    3/8/2019   3/15/2019 
      ---------------------------------------------------------
      12345   Widget         Inventory     50         25
      12345   Widget         TotSupply     50         25
      12345   Widget         SchedRcpts    50         25
      12345   Widget         ProjOH        100        50

      I've tried pivot/unpivot, but it continues to produce errors. I'm okay with running successive queries if needed to pivot the dates and then the values, but my attempts to just pivot or unpivot one of these two values (dates or supply values) produces errors. I'm new to Oracle pivot/unpivot and I'm connecting to an Oracle 11g database.

      I've also tried a union for the supply values, but that will produce about 800K rows for just the supply values prior to pivoting the dates to columns and that seems it would be very slow, but I will do that if it's my only choice. I'm using a read-only account, so I cannot create tables.

      Any help is appreciated

        • 1. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
          mathguy

          The unpivoting part is easy (you probably know that already).

           

          The standard advice for dynamic pivoting - which is what you need to get one column per date - is (in this order!)

           

          1. Don't do it. Dynamic pivoting is rarely needed. It is often wanted by clueless business users; our job is to educate them.
          2. If you must do it, use your reporting software to do it (when you do all the other formatting you need to do using the reporting software).
          3. If everything else fails, use dynamic SQL. There is a lot of advice (and examples) online, use Google - or the Search feature on this forum; this is a very frequently asked question.
          • 2. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
            Frank Kulash

            Hi,

             

            Dynamic SQL is the only way to get column names or aliases from the data.

             

            With plain old static SQL, you can put the dates on the first row of the result set, so it looks like a header.  See PIVOT SQL for an example.

            • 3. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
              BluShadow

              We have a community document that covers this topic as it's been asked so many times before...

               

              PL/SQL 101 : Cursors and SQL Projection

              • 4. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                Tim D

                Thanks BluShadow,

                 

                I would've never guessed to search for "Cursors and SQL Projection" :-)

                 

                I searched high and low for pivot/unpivot examples before posting a question.

                • 5. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                  Tim D

                  Hi MathGuy,

                   

                  Unfortunately, I am the reporting software ;-). We're using MS Access to connect via ODBC to the Oracle 11g backend and I'm trying to pull the data that is stored in the table this way into the way the user wants it to end up ultimately in a spreadsheet.

                   

                  I can write a function to do it (which is what most reporting software probably does as well), but it will be painstakingly slow and was hoping there was some SQL out there for a situation like this.

                  • 6. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                    mathguy

                    I may be mistaken - I haven't worked with Excel and VBA in a while - but doesn't Excel have relatively simple pivoting operations? That would count as the "reporting software" then.

                     

                    In any case - to do this in SQL, you do not need to write a function. You can write a preliminary SQL statement - or a short anonymous block - that will read the dates and create your actual SQL query, where the dates will have become hard-coded. See if you can make sense of this, using the document Blu provided. Or Google for "Oracle dynamic pivoting" - you will find plenty of examples and explanations.

                    • 7. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                      BluShadow

                      mathguy wrote:

                       

                      I may be mistaken - I haven't worked with Excel and VBA in a while - but doesn't Excel have relatively simple pivoting operations? That would count as the "reporting software" then.

                       

                      In any case - to do this in SQL, you do not need to write a function. You can write a preliminary SQL statement - or a short anonymous block - that will read the dates and create your actual SQL query, where the dates will have become hard-coded. See if you can make sense of this, using the document Blu provided. Or Google for "Oracle dynamic pivoting" - you will find plenty of examples and explanations.

                       

                       

                      Indeed, Excel does have pivoting built in, though not so easy to find on the toolbars as it used to be.

                      The shortcut for it is to highlight the data and then press ALT-D-W-P  (I remember it as the Department for Work and Pensions) which brings up the pivoting wizard.

                      • 8. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                        Tim D

                        Yes, Excel does have a pivot table function built in and it is easy to pivot the dates into column headers that are row data, but not so easy when you're simultaneously trying to unpivot 24 different value headers that need to become data elements in rows rather than headers. I'm sure there's an Excel guru out there somewhere that could come up with a way to do this using multiple pivots, VBA, or slicers, but that's not my wheelhouse.

                         

                        In any case, this was my solution:

                         

                        1. I was able to write a working pass-through pivot query to pivot the dates with one of the value headers:

                        SELECT * FROM

                        (

                        SELECT SCPOMGR.SKUPROJSTATIC.OPTIONSET

                        ,SCPOMGR.SKUPROJSTATIC.ITEM

                        ,SCPOMGR.SKUPROJSTATIC.LOC

                        ,SCPOMGR.ITEM.DESCR

                        ,SCPOMGR.ITEM.UOM

                        ,SCPOMGR.SKU.PLANLEVEL

                        ,SCPOMGR.SKU.UDC_VALUESTREAM AS ValueStream

                        ,SCPOMGR.SKU.UDC_ITEMTYPEDESC AS ItemTypeDesc

                        ,TO_CHAR(SCPOMGR.SKUPROJSTATIC.STARTDATE, 'mm/dd/yyyy') AS STARTDATE

                        ,SCPOMGR.SKUPROJSTATIC.PROJOH

                        ,'PROJOH' AS "PROJECTION COLUMNS"

                        FROM SCPOMGR.SKUPROJSTATIC

                        INNER JOIN SCPOMGR.ITEM ON

                        SCPOMGR.SKUPROJSTATIC.ITEM = SCPOMGR.ITEM.ITEM

                        INNER JOIN SCPOMGR.SKU ON

                        SCPOMGR.SKUPROJSTATIC.LOC = SCPOMGR.SKU.LOC AND SCPOMGR.SKUPROJSTATIC.ITEM = SCPOMGR.SKU.ITEM

                        WHERE (SCPOMGR.SKU.UDC_VALUESTREAM <> 'INACTIVE' OR SCPOMGR.SKU.UDC_VALUESTREAM Is Null)

                        AND SCPOMGR.SKUPROJSTATIC.LOC = 'HB'

                        AND SCPOMGR.SKUPROJSTATIC.OPTIONSET = 'WeeksEndFri' AND SCPOMGR.SKU.UDC_ITEMTYPEDESC <> 'Obsolete'

                        )

                        PIVOT

                        (

                        SUM(PROJOH)

                        FOR STARTDATE IN

                        ('03/15/2019','03/22/2019','03/29/2019','04/05/2019','04/12/2019','04/19/2019','04/26/2019','05/03/2019','05/10/2019','05/17/2019','05/24/2019','05/31/2019','06/07/2019','06/14/2019','06/21/2019','06/28/2019','07/05/2019','07/12/2019','07/19/2019','07/26/2019','08/02/2019','08/09/2019','08/16/2019','08/23/2019','08/30/2019','09/06/2019','09/13/2019','09/20/2019','09/27/2019','10/04/2019','10/11/2019','10/18/2019','10/25/2019','11/01/2019','11/08/2019','11/15/2019','11/22/2019','11/29/2019','12/06/2019','12/13/2019','12/20/2019','12/27/2019','01/03/2020','01/10/2020','01/17/2020','01/24/2020','01/31/2020','02/07/2020','02/14/2020','02/21/2020','02/28/2020','03/06/2020','03/11/2020')

                        );

                        2. Now that I have a working pivot query to pivot the dates for one of the value headers, I declared a couple variables on the MS Access side in VBA to store my dynamic dates and value headers.

                         

                        3. First, I'll use myDtRng variable to store my dynamic date string that will be used in the "FOR STARTDATE IN(" portion of my query. I pulled distinct dates in ascending chronological order from the Oracle table (it contains 52 rolling weeks) and cycled through the records appending to the myDtRng string variable until all dates were appended.

                         

                        4. myValCat is the second variable that I used to cycle through each value header, using VBA to dynamically write the pass-through query for each loop using both variables to substitute for my value header and the dates in the "FOR STARTDATE IN (" clause shown in the SQL above. I ran the query once for each value header, calling a function to make the table on the first loop and to append the values to that table on successive loops until all values for each value header have been written to the table.

                         

                        Done!

                        • 9. Re: Pivot Dynamic Dates to Columns and Value Headers to Rows
                          Tim D

                          And yes, I used TO_CHAR for the STARTDATE data as the STARTDATE field in the Oracle table is a datetime data type with various times associated with each date. If there is an easier or better way to format the dates and use them in the pivot, please feel free to let me know. As I said before, I'm an Oracle rookie when it comes to pivots and really any advanced Oracle SQL.