Hearing the Oracle

Home » Featured » More About PIVOT Queries

More About PIVOT Queries

complex pivot query with both multiple aggregates and multiple pivots

complex pivot query with both multiple aggregate functions and multiple pivot columns

An earlier post introduced the PIVOT feature in Oracle’s 11g2 SQL, and described the motivation for pivoting. This one explores a few more complex ways to use pivot queries. We’ll look at:

         • Multiple Pivot Columns
         • Multiple Aggregate Functions

within the same SQL statement and we’ll see a few errors which commonly arise when building more complex pivot queries like these. Finally, we’ll consider: why would one have a need to UNPIVOT?

Multiple Pivot Columns

Suppose you want to use multiple columns in the pivot argument. In this case you must list the paired column values (or triads — but they multiply quickly) you are interested in gathering, and provide for each a display name. Let’s say we are refining our original object counting query from the earlier post to inspect objects created within the past few months, and let’s also assume we are focusing in upon PL/SQL (or Java) objects and new tables and indexes.

                                                                                                                                           view original query 

The code objects, as a group, will consist of packages, procedures, functions, and triggers taken together. Similarly, the tables and indexes will be grouped as data objects. We also need to do a little work to construct groupings for creation months using the CTIME attribute in the SYS.OBJ$ table. The following snippet illustrates how we can reference objects created during the most recent three months:
w h i t e s p a c e

   rob@XE_11g2> SELECT
   to_char(add_months(sysdate, 0), ‘YYYYMM’) AS thismo,
   to_char(add_months(sysdate, -1), ‘YYYYMM’) AS lastmo,
   to_char(add_months(sysdate, -2), ‘YYYYMM’) AS prevmo
   FROM dual;

   THISMO  LASTMO  PREVMO
   ======  ======  ======
   201212  201211  201210

w h i t e s p a c e

Modifying the original query iteratively, I first applied TO_CHAR to the CTIME attribute, which is a DATE field, to gather the three most recent months. I came up with the following SQL, but received an error message.
w h i t e s p a c e


   SELECT * FROM
       (SELECT U.username, O.type#, TO_CHAR(O.ctime, ‘YYYYMM’) AS cmonth, O.obj#
        FROM sys.obj$ O, sys.dba_users U
        WHERE U.user_id = O.owner# (+)
        AND O.ctime > sysdate-100
        AND O.type# IN (1,2,7,8,9,12)
       )
   PIVOT
       (COUNT(obj#)
        FOR (type#, cmonth) IN
          ((2, TO_CHAR(add_months(sysdate, 0), ‘YYYYMM’)) AS TBL_CurrMO,
           (2, TO_CHAR(add_months(sysdate, -1), ‘YYYYMM’)) AS TBL_LastMO,
           (2, TO_CHAR(add_months(sysdate, -2), ‘YYYYMM’)) AS TBL_PrevtMO,
           (1, TO_CHAR(add_months(sysdate, 0), ‘YYYYMM’)) AS IDX_CurrMO,
           (1, TO_CHAR(add_months(sysdate, -1), ‘YYYYMM’)) AS IDX_LastMO,
           (1, TO_CHAR(add_months(sysdate, -2), ‘YYYYMM’)) AS IDX_PrevtMO
          )
       )
   /

w h i t e s p a c e

   rob@XE_11g2> @testPQM1
       ((2, to_char(add_months(sysdate, 0), ‘YYYYMM’)) AS TBL_CurrMO,
                               *
   ERROR at line 13:
   ORA-56901: non-constant expression is not allowed for pivot|unpivot values

w h i t e s p a c e

ORA-56900 and ORA-56901

A moment’s reflection and the problem seemed evident enough. Oracle doesn’t want to be calculating unpredictable values in the midst of distributing the fixed values of pivot columns. I went forward by removing the TO_CHAR function from the IN clause. Confident that I understood, I also added in the grouping logic for ‘data’ and ‘code’ in my next iteration, as shown below:
w h i t e s p a c e


   SELECT * FROM
      (SELECT
         U.username,
         DECODE(O.type#, 1,’data’,2,’data’, 7,’code’,8,’code’,9,’code’,12,’code’, ‘other’) AS objtype,
         TO_CHAR(O.ctime, ‘YYYYMM’) AS cmonth,
         O.obj#
       FROM sys.obj$ O, sys.dba_users U
       WHERE U.user_id = O.owner# (+)
       AND O.ctime > sysdate-100
      )
   PIVOT
      (COUNT(obj#)
       FOR (objtype, cmonth) IN
         ( (‘data’, :thismo) AS data_thisMO,
           (‘data’, :lastmo) AS data_lastMO,
           (‘data’, :prevmo) AS data_prevMO,
           (‘code’, :thismo) AS code_thisMO,
           (‘code’, :lastmo) AS code_lastMO,
           (‘code’, :prevmo) AS code_prevMO
         )
      )
   /

w h i t e s p a c e

I first populated the bind variables, and then ran the new query. But there were further limits demanded by PIVOT! A different though similar issue arose.
w h i t e s p a c e

   rob@XE_11g2> VAR thismo VARCHAR2(6);
   VAR lastmo VARCHAR2(6);
   VAR prevmo VARCHAR2(6);
   BEGIN
   SELECT to_char(add_months(sysdate, -1), ‘YYYYMM’),
   to_char(add_months(sysdate, -2), ‘YYYYMM’),
   to_char(add_months(sysdate, -3), ‘YYYYMM’)
   INTO :thismo, :lastmo, :prevmo
   FROM dual;
   END;
   /

   PL/SQL procedure successfully completed.


   rob@XE_11g2> @testPQM2
       ((‘data’, :thismo) AS TBL_CurrMO,
                 *
   ERROR at line 13:
   ORA-56900: bind variable is not allowed for pivot|unpivot values


   rob@XE_11g2> SELECT :thismo, :lastmo, :prevmo FROM dual;
   :THISMO    :LASTMO    :PREVMO
   =======    =======    =======
    201212     201211     201210

w h i t e s p a c e

complex pivot query with multi-column pivots

complex pivot query with multicolumn pivots

SQL*Plus still retains the bind variables; so the issue is with the pivot clause. Apparently Oracle does not even want to use explicit bind values within the IN clause under PIVOT. I got the query to work by hardcoding the value pairs and eliminating the bind variables. (See at left.) If you are puzzling over why PIVOT needs to be this picky, keep reading, as we will see below another example concerning pivot query value restrictions.
                                                                                                                       view corrected query results 

Multiple Aggregate Functions

It is also possible to pivot with several aggregate functions. In SQL, aggregate functions are normally associated with the GROUP BY clause. Look here to see the current compliment of aggregate functions available in release 11g2. Taking the Oracle-provided HR demo schema as our test data, we could ask: for each department compare the total salary, average salary, and headcounts between staff and managers. Note that in my own HR schema I significantly upgraded everybody’s salary because I found the 1950-style pay amounts to be bizarre and depressing — but this has no bearing on the outcome.

I needed first to create a distinction between staff and management employees. The HR.employees table gives an easy way to do this — we can simply check whether a given employee_id is in or out of the set of all manager’s employee_ids. I used a CASE WHEN SQL construct below (red) to do this. If you don’t know about them, they’re a handy way to assign different values to an attribute based on various conditions. It’s more powerful than a simple DECODE statement, a bit like having access to a little PL/SQL logic within SQL*Plus.
w h i t e s p a c e


   SELECT * FROM
   (
       SELECT
          D.department_name AS Organization,
          CASE
              WHEN employee_id IN
              (SELECT UNIQUE manager_id FROM employees) THEN ‘mgmt’
              ELSE ‘staff’
          END emptype,

          E.employee_id,
          E.salary
       FROM employees E, departments D
       WHERE E.department_id IS NOT NULL
       AND E.manager_id IS NOT NULL
       AND D.department_id = E.department_id
   )
   PIVOT
   (
       COUNT(employee_id) AS headcount,
       SUM(salary) AS totpay,
       AVG(salary) AS avgpay
       FOR (emptype) IN (‘staff’, ‘mgmt’)
   )
   /

w h i t e s p a c e

   rob@XE_11g2> @testPQM4
      FOR (emptype) IN
           *
   ERROR at line 22:
   ORA-56904: pivot value must have datatype that is convertible to pivot column

w h i t e s p a c e

Oops! Hope you don’t mind, but I find these PIVOT error messages interesting. What happened — why is it complaining about datatypes? I tried switching to simple NUMBER codes for these EMPTYPE values, 1 = staff and 2 = management, but to no avail. It seems once again that the optimizer wants explicit known values rather than having to compute them, as the CASE WHEN statement forces. But if an interim staging dataset is added, in which this categorizing of EMPTYPE is resolved, then we can use that dataset instead for the pivot phase.

So, next I tried making a table identical to HR.employees but for the addition of an explicit column containing our staff/mgmt identifier, using the same CASE WHEN snippet. This worked, in that I am able to show you a successful pivot query with multiple aggregate functions using it. (In case you’re wondering, using a view will not work; it fails with the same ORA-56904 error.)
                                                                                                                                    see attempt with View 
w h i t e s p a c e

   rob@XE_11g2> CREATE TABLE emp_4pivot AS
   SELECT E.*,
   CASE WHEN employee_id IN
      (SELECT UNIQUE manager_id FROM employees E) THEN ‘mgmt’
      ELSE ‘staff’ END emptype
   FROM employees E;

   Table created.


   rob@XE:11g2> desc emp_4pivot;
   Name             Null?      Type
   ===============  =========  ============
   EMPLOYEE_ID                 NUMBER(6)
   FIRST_NAME                  VARCHAR2(20)
   LAST_NAME        NOT NULL   VARCHAR2(25)
   EMAIL            NOT NULL   VARCHAR2(25)
   PHONE_NUMBER                VARCHAR2(20)
   HIRE_DATE        NOT NULL   DATE
   JOB_ID           NOT NULL   VARCHAR2(10)
   SALARY                      NUMBER(8,2)
   COMMISSION_PCT              NUMBER(2,2)
   MANAGER_ID                  NUMBER(6)
   DEPARTMENT_ID               NUMBER(4)
   EMPTYPE                     VARCHAR2(5)

w h i t e s p a c e

Now, once the query is modified to refer to the new table with the explicit EMPTYPE column, all works well. You’ll also notice that Oracle uses the aliases for the aggregate function columns appended to the terms you specify with the IN list, connected by an underscore, to construct the output’s column titles. So choose accordingly. Click the button at right to see the adjusted SQL and results.
                                                                                                                       view corrected query results 

Summing up, we’ve looked at expanding simple pivot queries in two ways. First, by having two separate data values defining our pivoted columns, like month and object type. And next, by asking for more than one aggregate function to be displayed for each pivoted column group. We’ve also seen both of these complications shown combined in a single query.

w h i t e s p a c e
Rule of Thumb: For multiple column pivot queries, specify the desired items after the FOR keyword. For multiple aggregate functions, specify the desired items before the FOR keyword:
w h i t e s p a c e


   SELECT * FROM
   (
       SELECT
          D.department_name AS Organization,
          E.emptype,
          E.employee_id,
          E.salary
       FROM emp_4pivot E, departments D
       WHERE E.department_id IS NOT NULL
       AND E.manager_id IS NOT NULL
       AND D.department_id = E.department_id
       ORDER BY 1
   )
   PIVOT
   (
       COUNT(employee_id) AS ct,              — Multiple Aggregate Functions
       SUM(salary) AS totpay,                       — Multiple Aggregate Functions
       AVG(salary) AS avgpay                       — Multiple Aggregate Functions
       FOR (emptype) IN
          (‘staff’, ‘mgmt’)                                  — Multiple Pivot Columns
   )
   /

w h i t e s p a c e

The Anti-Pivot

Oracle 11g2 also offers an UNPIVOT verb. Why would you want to do this? First let’s understand clearly what unpivoting is and is not. This quote is direct from Oracle’s documentation, my underlines:

An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

So the main point is that columns are being transposed into rows, which is the complimentary operation for pivoting. If you think about it, this need could arise in situations where denormalized data, spread out horizontally for presentation purposes perhaps, wants to be re-normalized. This is very often seen with spreadsheets. In this example, note how various expense and revenue items have been projected horizontally by time period. A future post will illustrate an UNPIVOT scenario in connection with importing data from spreadsheets into Oracle databases.
 
~RS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: