Ilmar Kerm

Oracle, databases, Linux and maybe more

Category: Blog entry

Written by:
Category: Blog entry
Published:

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 11.1.0.7 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);
commit;

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 
  UNPIVOT INCLUDE NULLS
    (monthly_value FOR month IN (jan, feb, mar, apr));

        ID MON MONTHLY_VALUE
---------- --- -------------
         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);
commit;

SELECT * FROM unpivot_ex2;

        ID JAN_INCOME JAN_EXPENSE FEB_INCOME FEB_EXPENSE MAR_INCOME MAR_EXPENSE
---------- ---------- ----------- ---------- ----------- ---------- -----------
         1          1           1          2           3          5           2
         2          5           6          4           4

UNPIVOT allows to create column groups, like this:

SELECT * FROM unpivot_ex2
  UNPIVOT INCLUDE NULLS
    ((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
         2 MAR_INCOME_MAR_EXPENSE

6 rows selected.

How cool is that? 🙂

Ilmar Kerm