Unpivot, convert columns into rows

Imagine you have a table with all data about all your friends. It could be something like this:

create table myfriends
  (name      varchar2(40)
  ,address   varchar2(60)
  ,residence varchar2(60)
  ,phone     varchar2(10)
  ,email     varchar2(60)
  );

It started with one email address for each friend. Later, some friends told you they had two email addresses and several months later some friends even had three email addresses (boasters).  Meanwhile your table has three columns with email addresses:

ALTER TABLE MYFRIENDS
ADD (EMAIL2 VARCHAR2(60) );

ALTER TABLE MYFRIENDS
ADD (EMAIL3 VARCHAR2(60) );

And this are some of your friends

insert into myfriends(name,email,email2,email3) values ('goofy','goofy@disney.com',null,null);
insert into myfriends(name,email,email2,email3) values ('donald','donald@disney.com','donald@disney.fr','donald.duck@gmail.com');
insert into myfriends(name,email,email2,email3) values ('gus goose','gus@disney.com','gusgoose@disney.eu',null);

For some reason you need for each friend, a list of his addresses, so that you can choose the right address to send him an email. Most of us will probably solve this using a query with three unions, and not forget, three times a where clause.

select name, email from myfriends
  where email is not null
union
select name, email2 from myfriends
  where email2 is not null
union
select name, email3 from myfriends
  where email3 is not null

And there they are, the requested email addresses of our friends.

NAME       EMAIL
=========  ======================================
donald     donald.duck@gmail.com
donald     donald@disney.com
donald     donald@disney.fr
goofy      goofy@disney.com
gus goose  gus@disney.com
gus goose  gusgoose@disney.eu

Since Oracle 11g there is a very good alternative, the UNPIVOT operator. Use the UNPIVOT operator after the FROM clause to convert columns into rows.

select name, emailaddress
  from myfriends
  unpivot (
            (emailaddress) for x
               in (email, email2, email3)
          )

This looks a lot more readable to me. Furthermore, the maintenance cost of the first query (with All that unions) is much higher and the execution time much longer due to the sorting and merging required with the series of UNION statements and multiple passes over the same table.
More info about UNPIVOT and PIVOT: oracle-database-11g-top-features
Succes with the unpivot and pivot operations and greetings from my friends donald, goofy and gus.

Ratings:

VN:F [1.9.13_1145]

please wait...

Rating: 4.5/5 (2 votes cast)

Tags: Oracle 11g, pivot, pl/sql, unpivot

• 

 Print This PDF Version