Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to UNPIVOT results in Oracle?
Problem:
You want to UNPIVOT results in Oracle.
Solution
The UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time,allow you to run an aggregation function on the data.
Consider a table called customer which has below data stored inside.
Example
SELECT * FROM customers;
Output
1 tammy.bryant@internalmail Tammy Bryant 2 roy.white@internalmail Roy White 3 gary.jenkins@internalmail Gary Jenkins 4 victor.morris@internalmail Victor Morris 5 beverly.hughes@internalmail Beverly Hughes
In the customer table we can see that full name has First name and last name , now lets seperate the First Name and Last Name into different columns.
Example
SELECT full_name, SUBSTR(full_name,1,INSTR(full_name,' ',1,1)-1) first_name, SUBSTR(full_name,INSTR(full_name,' ',-1) + 1) last_name FROM customers;
Output
Tammy Bryant Tammy Bryant Roy White Roy White Gary Jenkins Gary Jenkins Victor Morris Victor Morris Beverly Hughes Beverly Hughes Evelyn Torres Evelyn Torres
Now if we want these first name and last name columns to be coverted into a one single column , we can use UNPIVOT function of oracle.
Example
SELECT DISTINCT new_column FROM ( SELECT full_name, SUBSTR(full_name,1,INSTR(full_name,' ',1,1)-1) first_name, SUBSTR(full_name,INSTR(full_name,' ',-1) + 1) last_name FROM customers ) UNPIVOT (new_column FOR ref_col2 IN (first_name,last_name));
Output
Roy Beverly Carl Sanchez Evans Martinez Dennis Brown Deborah Carolyn Bennett Jack
Data Preparation: Data used for the problem is shown below.
Example
create table customers ( customer_id integer generated by default on null as identity, email_address varchar2(255 char) not null, full_name varchar2(255 char) not null) ; insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (1,'tammy.bryant@internalmail','Tammy Bryant'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (2,'roy.white@internalmail','Roy White'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (3,'gary.jenkins@internalmail','Gary Jenkins'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (4,'victor.morris@internalmail','Victor Morris'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (5,'beverly.hughes@internalmail','Beverly Hughes'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (6,'evelyn.torres@internalmail','Evelyn Torres'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (7,'carl.lee@internalmail','Carl Lee'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (8,'douglas.flores@internalmail','Douglas Flores'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (9,'norma.robinson@internalmail','Norma Robinson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (10,'gregory.sanchez@internalmail','Gregory Sanchez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (11,'judy.evans@internalmail','Judy Evans'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (12,'jean.patterson@internalmail','Jean Patterson'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (13,'michelle.ramirez@internalmail','Michelle Ramirez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (14,'elizabeth.martinez@internalmail','Elizabeth Martinez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (15,'walter.rogers@internalmail','Walter Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (16,'ralph.foster@internalmail','Ralph Foster'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (17,'tina.simmons@internalmail','Tina Simmons'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (18,'peter.jones@internalmail','Peter Jones'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (19,'kathryn.rogers@internalmail','Kathryn Rogers'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (20,'dennis.lopez@internalmail','Dennis Lopez'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (21,'martha.baker@internalmail','Martha Baker'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (22,'raymond.bailey@internalmail','Raymond Bailey'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (23,'christopher.allen@internalmail','Christopher Allen'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (24,'jonathan.coleman@internalmail','Jonathan Coleman'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (25,'walter.turner@internalmail','Walter Turner'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (26,'anna.murphy@internalmail','Anna Murphy'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (27,'carol.alexander@internalmail','Carol Alexander'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (28,'teresa.brown@internalmail','Teresa Brown'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (29,'beverly.rivera@internalmail','Beverly Rivera'); insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (30,'lisa.hughes@internalmail','Lisa Hughes'); COMMIT;
3K+ Views
