Rittman Mead - Delivered Intelligence

We briefly interrupt the transactional schemas series for this important message: the PIVOT clause of SELECT is only for aggregates! When Oracle added the PIVOT and UNPIVOT operators in Database 11g, at first, I was hopeful. It certainly scratched an itch that had been around in the Oracle DW space (and especially… the ETL arena) for some time. But once I got my hands on it for a test drive, I was bit disappointed. You see… it only works with aggregate functions. Let me give a quick demonstration. Say we want to look at the Oracle data dictionary and query all the columns for a particular table, we would write the following query:

SQL> SELECT column_name
  2  FROM user_tab_columns
  3  WHERE table_name='SALES';

COLUMN_NAME
--------------------------------
PROD_ID
CUST_ID
TIME_ID
CHANNEL_ID
PROMO_ID
QUANTITY_SOLD
AMOUNT_SOLD

7 rows selected.

SQL>

But how would we display this in tabular format? You know… instead of returning 7 rows, return one row with 7 columns. Doing this prior to Oracle 11g, we basically had three options: using DECODE, using CONNECT BY, or using the SQL MODEL clause. At one time or another, I have written versions of all of these. The attached link shows Tom Kyte talking through several approaches, and what’s clear from all of them is this: it’s way too complicated to do in SQL what Excel can do for us quite simply. What we needed was a PIVOT clause… and then “Presto!”, we got one.

But it wasn’t what I expected. The Oracle PIVOT clause is only for aggregate functions… so useful in generating reports with measures in them. For instance, I could easily do a report on how many instances of a particular column exist, but I couldn’t seemingly provide a query that actually shows the literal value. Let me demonstrate:

 SQL> SELECT *
  2    FROM (
  3           SELECT column_name
  4             FROM user_tab_columns
  5            WHERE table_name='SALES'
  6         )
  7         pivot
  8         ( count(*)
  9           FOR column_name IN (
 10                                'PROD_ID' AS PROD_ID,
 11                                'CUST_ID' AS CUST_ID,
 12                                'TIME_ID' AS TIME_ID,
 13                                'CHANNEL_ID' AS CHANNEL_ID,
 14                                'PROMO_ID' AS PROMO_ID,
 15                                'QUANTITY_SOLD' AS QUANTITY_SOLD,
 16                                'AMOUNT_SOLD' AS AMOUNT_SOLD
 17                              )
 18         );

   PROD_ID |    CUST_ID |    TIME_ID | CHANNEL_ID |   PROMO_ID | QUANTITY_SOLD | AMOUNT_SOLD
---------- | ---------- | ---------- | ---------- | ---------- | ------------- | -----------
         1 |          1 |          1 |          1 |          1 |             1 |           1

1 row selected.

SQL>

Following directly after the PIVOT keyword, we have to provide an aggregate function: COUNT, SUM, AVG, etc. So if I just wanted to display the actual value of the COLUMN_NAME attribute at the top of the chart, this doesn’t seem to be an option:

SQL> SELECT *
  2    FROM (
  3           SELECT column_name
  4             FROM user_tab_columns
  5            WHERE table_name='SALES'
  6         )
  7         pivot
  8         ( column_name
  9           FOR column_name IN (
 10                                'PROD_ID' AS PROD_ID,
 11                                'CUST_ID' AS CUST_ID,
 12                                'TIME_ID' AS TIME_ID,
 13                                'CHANNEL_ID' AS CHANNEL_ID,
 14                                'PROMO_ID' AS PROMO_ID,
 15                                'QUANTITY_SOLD' AS QUANTITY_SOLD,
 16                                'AMOUNT_SOLD' AS AMOUNT_SOLD
 17                              )
 18         );
       ( column_name
         *
ERROR at line 8:
ORA-56902: expect aggregate function inside pivot operation

SQL>

Does anyone see the solution? I didn’t at first, until Pete Tamisin gave me the answer today. When I hear “aggregate function”, my mind usually goes straight toward the NUMBER datatype. Perhaps that’s from years of working with BI Tools. But there are plenty of aggregate functions that accept any datatype… even VARCHARS. For instance… the MAX function. Yep… that’s the answer:

SQL> SELECT *
  2    FROM (
  3           SELECT column_name
  4             FROM user_tab_columns
  5            WHERE table_name='SALES'
  6         )
  7         pivot
  8         ( MAX(column_name)
  9           FOR column_name IN (
 10                                'PROD_ID' AS PROD_ID,
 11                                'CUST_ID' AS CUST_ID,
 12                                'TIME_ID' AS TIME_ID,
 13                                'CHANNEL_ID' AS CHANNEL_ID,
 14                                'PROMO_ID' AS PROMO_ID,
 15                                'QUANTITY_SOLD' AS QUANTITY_SOLD,
 16                                'AMOUNT_SOLD' AS AMOUNT_SOLD
 17                              )
 18         );

PROD_ID    | CUST_ID    | TIME_ID    | CHANNEL_ID | PROMO_ID   | QUANTITY_SOLD  | AMOUNT_SOLD
---------- | ---------- | ---------- | ---------- | ---------- | -------------- | ------------
PROD_ID    | CUST_ID    | TIME_ID    | CHANNEL_ID | PROMO_ID   | QUANTITY_SOLD  | AMOUNT_SOLD

1 row selected.

SQL>

Of course… you have to ensure that the query is one for which only a single row is returned for each column you want to generate from a row.

Posted in Data Warehousing, Oracle Database | 6 Comments »