Oracle 11g: New Pivot Table Feature

Oct 10, 2007 / By Robert Hamel

Tags: ,

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.

8 Responses to “Oracle 11g: New Pivot Table Feature”

  • Mr. Ed says:

    2 out of 10. At this rate, it won’t work properly until Oracle 13g Release 1.

  • ghassan says:

    Robert,
    Doing what you want will not be easy, not only for Oracle, but for the clients: Supporting the sub-query and ANY without XML implies that CBO not only parses the SQL, but also executes the sub-query (or the query if you use ANY) in order to find out how many columns this query returns (it has to know in order to tell the ‘client’ programs how many columns there are so that they allocate the buffers, ….). A way out would be to augment the SQL with the MAX number of columns the user expects, but this puts much burden on the user, and the system will have to decide what to do in case of ANY surpassing this MAX.

  • [...] difference between count(*) and count(1). Also here at home, Robert Hamel gives a bad rating to 11g’s new pivot table feature. “I was very pleased when I heard about Oracle adding pivot functionality in select [...]

  • Mathew says:

    excelent functionality but the usage is very limited with the restriction of variable value in the pivot section.I dont know why the Orcale more concentrated with XML intsted of providing the functionality in the base noraml SQL.

  • MARTIN says:

    Hello, Can anyone help me? Ive been trying a simple pivot byt just cant get it to work.

    * i need the below select statement to have an output as below:
    SELECT BGROUP, REFNO, SEQNO, PP02D, PP05P
    FROM PRESERVED_PENSIONER_HISTORY
    WHERE REFNO IN (’0084941′,’0079500′,
    ’0082833′,’0094388′,’0063814′,’0081368′)

    *OUTPUT

    REFNO DATE1(PP02D) DATE2(PP02D)
    0084941 PP05P PP05P
    0079500 PP05P PP05P
    0082833 PP05P PP05P

    There are many values for both values PP02D and PP05P.
    Any help will be appreciated.

  • Chris says:

    I’m currently writing some code that requires a pivot table. All I need to do is generate an output file with the pivoted data, and I’m doing it using SQL*Plus. In this case, I’m just going to generate the IN list using SQL-generating-SQL. I’ve got a variable list of values from 120-140 values going across the columns, so I can’t make it static. The SQL-generating-SQL technique works fine for this case. Even if I was writing a C/C++ program, I’d still use dynamic SQL for this and allocate my return buffers in a variable sized array. I personally don’t see this as a huge limitation of the pivot table feature in 11g. Relying on XML to make the columns variable was probably a good decision on Oracle’s part. XML gives you an overall structure to handle the returning data rather than using an array like I would do. My approach works, but it’s probably a little old-school. Using XML is more forward-thinking if you ask me.

  • Manan Sanghvi says:

    Hey,
    I am using the Oracle Warehouse Builder 11G. I am having an issue with the pivot operator. IN my source database I have a table with around 200 columns, each having say 1000 rows. Now I want to make each of the column into multiple rows by pivoting it. IN the pivot operator provided I have to type in the name of the columns manually which is not feasible for such big tables. Any one have any suggestions??

  • [...] We have someone who needs to do something like this. Right now the plan is to create some dynamic SQL. You may find this page useful. Oracle 11g: New Pivot Table Feature | The Pythian Blog [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>