Oracle 11g: New Pivot Table Feature

I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = 'X' then amount else 0 end) total_X for each column we would want to display.

I am basically looking for three things in a pivot-style query:

1. the ability to specify which column will be pivoted as one or more columns in the resulting query
2. row subtotals
3. column subtotals

The first item is the only one that really matters. I can work around the other two, so let’s get started.

Create a test table with a few rows:

create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));
insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy');
insert into bob_pivot_test values (1,10.99,sysdate-9,'Toy');
insert into bob_pivot_test values (1,20.999,sysdate-9,'Toy');
insert into bob_pivot_test values (1,20.999,sysdate-9,'Tool');
insert into bob_pivot_test values (1,20.999,sysdate-9,'Furniture');
insert into bob_pivot_test values (1,20.999,sysdate-9,'Food');
insert into bob_pivot_test values (1,5.00,sysdate-8,'Toy');
insert into bob_pivot_test values (1,5.00,sysdate-8,'Tool');
insert into bob_pivot_test values (1,5.00,sysdate-8,'Furniture');
insert into bob_pivot_test values (1,5.00,sysdate-8,'Food');
insert into bob_pivot_test values (1,5.00,sysdate-7,'Toy');
insert into bob_pivot_test values (1,5.00,sysdate-7,'Tool');
insert into bob_pivot_test values (1,5.00,sysdate-7,'Furniture');
insert into bob_pivot_test values (1,5.00,sysdate-7,'Food');

I looked at the syntax diagram and came up with the following query to start:

SELECT * FROM
(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)
PIVOT
(SUM(cost) FOR product_type IN ('Toy','Food'));  2    3    4

DAY            'Toy'     'Food'
--------- ---------- ----------
08-SEP-07     31.989     20.999
09-SEP-07         10         10
07-SEP-07      10.99
10-SEP-07         10         10

It’s a good start, but useful only if we don’t have to specify the columns. I tried and tried, then read a little further in the documentation, and found that the ANY or subquery that can be specified in the IN clause for product_type works only if you pivot for XML. What a disappointment!

This excerpt is straight from the documentation:

subquery

A subquery is used only in conjunction with the XML keyword. When you specify a subquery,
all values found by the subquery are used for pivoting. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the pivot_in_clause, the subquery produces a single XML string column. The XML string for each row holds aggregated data corresponding to the implicit GROUP BY value of that row. The XML string for each output row includes all pivot values found by the subquery, even if there are no corresponding rows in the input data.

The subquery must return a list of unique values at the execution time of the pivot query. If the subquery does not return a unique value, then Oracle Database raises a run-time error. Use the DISTINCT keyword in the subquery if you are not sure the query will return unique values.

ANY

The ANY keyword is used only in conjunction with the XML keyword. The ANY keyword acts as a wildcard and is similar in effect to subquery. The output is not the same cross-tabular format returned by non-XML pivot queries. Instead of multiple columns specified in the pivot_in_clause, the ANY keyword produces a single XML string column.

The XML string for each row holds aggregated data corresponding to the implicit GROUP BY value of that row. However, in contrast to the behavior when you specify subquery, the ANY wildcard produces an XML string for each output row that includes only the pivot values found in the input data corresponding to that row.

Okay, so we move on. Let’s see if the XML version of the query actually works. Here it is, along with its data:

SQL> set long 10000000
SQL> SELECT * FROM
(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)
PIVOT XML(SUM(cost) as cost FOR product_type IN (ANY));  2    3

DAY
---------
PRODUCT_TYPE_XML
--------------------------------------------------------------------------------
07-SEP-07
<PivotSet><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">10.99</column></item></PivotSet>

08-SEP-07<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST"
>20.999</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Tool</column><column name = "COST">20.999</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">31.989</column></item></PivotSet>

09-SEP-07
<PivotSet><item><column name = "PRODUCT_TYPE">Food</column><column name = "COST">10</column></item><item><column name = "PRODUCT_TYPE">Furniture</column><column name = "COST">10</column></item><item><column name = "PRODUCT_TYPE">Tool</column><column name = "COST">10</column></item><item><column name = "PRODUCT_TYPE">Toy</column><column name = "COST">10</column></item></PivotSet>

Well it works, but it’s not much of a breakthrough. You might as well create the columns with sums of case statements if you have to define them. But let’s move on to the sums of rows and columns, starting with a grand total at the bottom:

select day, sum(toy), sum(food) from (
SELECT * FROM
(SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)
PIVOT
(SUM(cost) FOR product_type IN ('Toy' ,'Food' ))
)
group by cube(day);  2    3    4    5    6    7
select day, sum(toy), sum(food) from (
*
ERROR at line 1:
ORA-00904: "FOOD": invalid identifier

If we look at the last query that worked, we notice that the column names are the actual mixed-cased values in single quotes. We have to alias them, which would make sense for numeric values as well, so lets try that.

select day, sum(toy), sum(food) from (
2      SELECT * FROM
3      (SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)
4      PIVOT
5    (SUM(cost) FOR product_type IN ('Toy' as "TOY",'Food' as "FOOD"))
6  )
7  group by cube(day)
8     order by grouping(day), day;

DAY         SUM(TOY)  SUM(FOOD)
--------- ---------- ----------
07-SEP-07      10.99
08-SEP-07     31.989     20.999
09-SEP-07         10         10
10-SEP-07         10         10
62.979     40.999

That wasn’t so bad. Now onto row-totals on the right. You have to do it the old-fashioned way.

SQL>  select day, sum(toy), sum(food),sum(nvl(toy,0)+nvl(food,0)) from (
2  SELECT * FROM
3  (SELECT trunc(tx_date,'dd') day , product_type, cost FROM bob_pivot_test)
4  PIVOT
5  (SUM(cost) FOR product_type IN ('Toy' as "TOY",'Food' as "FOOD"))
)
group by cube(day)
6    7    8  order by grouping(day), day;

DAY         SUM(TOY)  SUM(FOOD) SUM(NVL(TOY,0)+NVL(FOOD,0))
--------- ---------- ---------- ---------------------------
07-SEP-07      10.99                                  10.99
08-SEP-07     31.989     20.999                      52.988
09-SEP-07         10         10                          20
10-SEP-07         10         10                          20
62.979     40.999                     103.978

So we were able to get the data out, but nonetheless, I feel that Oracle let us down by forcing us to specify columns. I know it’s not easy to do, but that’s why I was excited about the feature in the first place. I would give this feature a 2 out of 10. I hope that in the next release, Oracle will make available for regular SQL the clauses now available only for XML.

So now, dear reader, we hope we have helped you figure out something you needed to know. It turns out that you can help us here at Pythian with something we need to know! If you are aware of a DBA requirement within your organization, salaried or consulting, please pop in your email address here:

We respect your privacy and will not share your address with any third party. As a thank you for just participating, we will enter you into a monthly draw for a year’s membership in the ACM, which includes access to 600 books from the O’Reilly Bookshelf Online, 500 books from Books24x7 and 3000 online courses from SkillSoft, including tons of courseware on Oracle, SQL Server, and MySQL.