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

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

w h i t e s p a c e

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

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

w h i t e s p a c e

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

w h i t e s p a c e

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

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

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