Ilmar Kerm

Oracle, databases, Linux and maybe more

Welcome to my first blog 🙂 And I’ll start with something simple, but very cool at the same time.

I really like Oracle 11g database and one of my favourite features is the PIVOT/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as rows. I have found them very useful for APEX applications, when I need to present data in table rows as columns in an APEX form for user input.

PIVOT feature seems to have recieved a lot of bloggers attention, but I haven’t read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database Enterprise Edition for all the examples here):

create table unpivot_ex1 (
 id number primary key,
 jan number,
 feb number,
 mar number,
 apr number

Fill it up with test data:

insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (1, 1, 2, null, 4);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (2, 5, 6, null, 8);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (3, 9, 10, null, null);

SELECT * FROM unpivot_ex1;

    ID        JAN        FEB        MAR        APR
---------- ---------- ---------- ---------- ----------
     1          1          2                     4
     2          5          6                     8
     3          9         10

Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:

SELECT * FROM unpivot_ex1 
    (monthly_value FOR month IN (jan, feb, mar, apr));

---------- --- -------------
         1 JAN             1
         1 FEB             2
         1 MAR
         1 APR             4
         2 JAN             5
         2 FEB             6
         2 MAR
         2 APR             8
         3 JAN             9
         3 FEB            10
         3 MAR
         3 APR

12 rows selected.

Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:

create table unpivot_ex2 (
  id number primary key,
  jan_income number,
  jan_expense number,
  feb_income number,
  feb_expense number,
  mar_income number,
  mar_expense number

For every month there is income and expense column.

insert into unpivot_ex2 values (1, 1, 1, 2, 3, 5, 2);
insert into unpivot_ex2 values (2, 5, 6, 4, 4, null, null);

SELECT * FROM unpivot_ex2;

---------- ---------- ----------- ---------- ----------- ---------- -----------
         1          1           1          2           3          5           2
         2          5           6          4           4

UNPIVOT allows to create column groups, like this:

SELECT * FROM unpivot_ex2
    ((income, expense) FOR month IN (
     (jan_income, jan_expense), (feb_income, feb_expense), 
     (mar_income, mar_expense)));

        ID MONTH                      INCOME    EXPENSE
---------- ---------------------- ---------- ----------
         1 JAN_INCOME_JAN_EXPENSE          1          1
         1 FEB_INCOME_FEB_EXPENSE          2          3
         1 MAR_INCOME_MAR_EXPENSE          5          2
         2 JAN_INCOME_JAN_EXPENSE          5          6
         2 FEB_INCOME_FEB_EXPENSE          4          4

6 rows selected.

How cool is that? 🙂


  1. Stew says:


    That's very cool, thanks for showing the correct syntax. I wasn't able to figure it out from the documentation.

    Now, if there was a way to define value aliases (e.g. Month = '1' instead of the cumbersome 'JAN_INCOME_JAN_EXPENSE', that would be *very* cool!

  2. Stew,

    If I understand your question, all you need to do is…

    (jan_income,jan_expense) AS 'january',
    (feb_income,feb_expense) AS 'february'

    Hope this helps,


  3. Stew says:


    Thanks for the reply.

    That did the trick!

  4. chenroger says:

    When I run a similar code as above it returns:

    [Err] ORA-00979: not a GROUP BY expression

  5. Thanks for posting a useful example, the official documentation is often useless when it comes to showcase certain features with non-trivial scenarios.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.