PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1

This article shows how to use the new PIVOT and UNPIVOT operators in 11g, as well as giving a pre-11g solution to the same problems.

PIVOT

The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. To see the PIVOT operator in action we need to create a test table.

create table pivot_test (

  id            number,

  customer_id   number,

  product_code  varchar2(5),

  quantity      number

);

insert into pivot_test values (1, 1, 'A', 10);

insert into pivot_test values (2, 1, 'B', 20);

insert into pivot_test values (3, 1, 'C', 30);

insert into pivot_test values (4, 2, 'A', 40);

insert into pivot_test values (5, 2, 'C', 50);

insert into pivot_test values (6, 3, 'A', 60);

insert into pivot_test values (7, 3, 'B', 70);

insert into pivot_test values (8, 3, 'C', 80);

insert into pivot_test values (9, 3, 'D', 90);

insert into pivot_test values (10, 4, 'A', 100);

commit;

So our test data starts off looking like this.

select * from pivot_test;

        ID CUSTOMER_ID PRODU   QUANTITY

---------- ----------- ----- ----------

         1           1 A             10

         2           1 B             20

         3           1 C             30

         4           2 A             40

         5           2 C             50

         6           3 A             60

         7           3 B             70

         8           3 C             80

         9           3 D             90

        10           4 A            100

10 rows selected.

SQL>

In its basic form the PIVOT operator is quite limited. We are forced to list the required values to PIVOT using the IN clause.

select *

from   (select product_code, quantity

        from   pivot_test)

pivot  (sum(quantity) as sum_quantity for (product_code) in ('A' as a, 'B' as b, 'C' as c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY

-------------- -------------- --------------

           210             90            160

1 row selected.

...

UNPIVOT

The UNPIVOT operator converts column-based data into separate rows. To see the UNPIVOT operator in action we need to create a test table.

create table unpivot_test (

  id              number,

  customer_id     number,

  product_code_a  number,

  product_code_b  number,

  product_code_c  number,

  product_code_d  number

);

insert into unpivot_test values (1, 101, 10, 20, 30, null);

insert into unpivot_test values (2, 102, 40, null, 50, null);

insert into unpivot_test values (3, 103, 60, 70, 80, 90);

insert into unpivot_test values (4, 104, 100, null, null, null);

commit;

So our test data starts off looking like this.

select * from unpivot_test;

        ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D

---------- ----------- -------------- -------------- -------------- --------------

         1         101             10             20             30

         2         102             40                            50

         3         103             60             70             80             90

         4         104            100

4 rows selected.

SQL>

The UNPIVOT operator converts this column-based data into individual rows.

...

Ver documento en: 

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

Fuentes

Artículo:   "PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1" Publicado en https://oracle-base.com/ por VÁZQUEZ LÓPEZ ABOGADOS el 11 abr 2025.  Consultado el 15 abr 2025.

URL: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1