Grassroots Oracle: Demystifying Oracle Unpivot

Friday, 22 August 2014

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.

Problem

I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.

create table aus_attractions(id  number, city varchar2(50)
  , attraction1 varchar2(50)
  , attraction2 varchar2(50)
  , reason1 varchar2(50)
  , reason2 varchar2(50)
  );
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand');
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic');
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds');

select id, city
      ,attraction1,attraction2
      ,reason1, reason2
from aus_attractions;

Not optimal relational data design

I'd like to see each attraction by row - 6 rows instead of 3.

You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.

Solution

Then we can turn our original statement into an inline view, serving the unpivot function.

select id, city, attraction, reason, rec_nbr
from ( -- original query:
     (select id, city
         ,attraction1,attraction2
         ,reason1, reason2
   from aus_attractions
   )
 unpivot               -- the magic operator
 ((attraction, reason) -- names of replacement columns
  for rec_nbr in (     -- new column defining data source in literal alias below
   -- split each group of fields in here
   (attraction1, reason1) as 'REC1' 
  ,(attraction2, reason2) as 'REC2'
  )
 )
);

ID      CITY       ATTRACTION        REASON       REC_NBR
------  ---------- ----------------- ------------ -------
1       Perth      weather           sunny        REC1
1       Perth      beaches           white sand   REC2
2       Sydney     bridge            climb        REC1
2       Sydney     blue mountains    scenic       REC2
3       Melbourne  culture           activities   REC1
3       Melbourne  aussie rules      crowds       REC2

6 rows selected

Awesome.