Pivot and UnPivot Examples in Oracle

Hi, 

Today I am explaining you about two simple functions;but powerful as it can be used for data warehousing application. Those are mentioned below. 

Pivot Functions : Pivot Functions basically converts a row information into a column. Assume we have a table "sales_sample" with the structure as mentioned below.

view plaincopy to clipboardprint?

1. create table sales_sample ( sale_region varchar2(30), sale_year number,  sale_month varchar2(20), sale_product_code varchar2(30), quantity  number )   

I am populating the table with the following values. 

view plaincopy to clipboardprint?

1. insert into sales_sample values('North America',2014,'Jan','Iphone',100);  
2.   
3. insert into sales_sample values('North America',2014,'Jan','Samsung',200);  
4.   
5. insert into sales_sample values('North America',2014,'Jan','Nexus',300);  
6.   
7. insert into sales_sample values('Asia Pacific',2014,'Jan','Iphone',510);  
8.   
9. insert into sales_sample values('Asia Pacific',2014,'Jan','Samsung',220);  
10.   
11. insert into sales_sample values('Asia Pacific',2014,'Jan','Nexus',320);  
12.   
13. insert into sales_sample values('North America',2014,'Feb','Iphone',11);  
14.   
15. insert into sales_sample values('North America',2014,'Feb','Samsung',22);  
16.   
17. insert into sales_sample values('North America',2014,'Feb','Nexus',33);  
18.   
19. insert into sales_sample values('Asia Pacific',2014,'Feb','Iphone',44);  
20.   
21. insert into sales_sample values('Asia Pacific',2014,'Feb','Samsung',55);  
22.   
23. insert into sales_sample values('Asia Pacific',2014,'Feb','Nexus',66);  
24.   
25. SQL>select * from sales_sample;  
26.   
27. SALE_REGION    SALE_YEAR  SALE_MONTH  SALE_PRODUCT_CODE   QUANTITY  
28. --------------------------------------------------------------------------------------------  
29. North America   2014             Jan                  Iphone                            100    
30. North America   2014             Jan                  Samsung                           200    
31. North America   2014             Jan                  Nexus                             300    
32. North America   2014             Feb                  Iphone                            11    
33. North America   2014             Feb                  Samsung                           22    
34. North America   2014             Feb                  Nexus                             33    
35. Pacific         2014             Jan                  Iphone                            510    
36. Pacific         2014             Jan                  Samsung                           220    
37. Pacific         2014             Jan                  Nexus                             320    
38. Pacific         2014             Feb                  Iphone                            44    
39. Pacific         2014             Feb                  Samsung                           55    
40. Pacific         2014             Feb                  Nexus                             66    
41. -----------------------------------------------------------------------------------------

Now apply the pivot function on the data set; the syntax is mentioned below 

Pivot Function which will give region,year and month level aggregates of totla quantity of products sold. 

view plaincopy to clipboardprint?

1. select * from   
2. (  
3. select sale_region, SALE_YEAR, sale_month,sale_product_code,quantity from sales_sample ) PIVOT  
4. (  
5. SUM(quantity)   
6.   FOR sale_product_code IN ('Iphone' AS IPHONE_QUANTITY, 'Samsung' SAMSUNG_QUANTITY,'Nexus' NEXUS_QUANTITY)   
7. )  
8. ORDER BY SALE_REGION,sale_month  
9.   
10. Output  
11. --------  
12.   
13. REGION           YEAR     MONTH  IPHONE_QUANTITY  SAMSUMG_QUANITY   NEXUS_QUANTITY   
14. ----------------------------------------------------------------------------------------- ---------------------  
15. Asia Pacific        2014      Feb           44                        55                            66    
16. Asia Pacific        2014      Jan           510                       220                           320    
17. North America       2014      Feb           11                        22                            33    
18. North America       2014      Jan           100                       200                           300    
19. --------------------------------------------------------------------------------------------------------------  

Un Pivot Functions : Converts a column into a corresponding row. It is opposite process of pivoting.

UnPivot Example 
-------------------- 

view plaincopy to clipboardprint?

1. create table sales_region_year(  
2. SALE_REGION           VARCHAR2(30)   
3. SALE_YEAR             NUMBER         
4. IPHONE_QUANTITY       NUMBER         
5. SAMSUNG_QUANTITY      NUMBER         
6. NEXUS_QUANTITY        NUMBER     
7. )  
8.   
9. SELECT * FROM SALES_REGION_YEAR   
10. UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (IPHONE_QUANTITY AS 'Iphone', SAMSUNG_QUANTITY AS 'Samsung', NEXUS_QUANTITY AS 'Nexus'));  
11.   
12. SQL>SELECT * FROM SALES_REGION_YEAR ;  
13.   
14. SALE_REGION    SALE_YEAR    PRODUCT_CODE   QUANTITY  
15. ------------------------------------------------------------------  
16. North America  2014         Iphone               111  
17. North America  2014         Samsung              222  
18. North America  2014         Nexus                333  
19. Pacific        2014         Iphone               554  
20. Pacific        2014         Samsung              275  
21. Pacific        2014         Nexus                386  
22. -------------------------------------------------------------------- 

Hope it is very informative.