I was amazed at how handy the Pivot function can be. It empowers the user to provide aggregate and denormalized view of a data set which could be a query or a table. In this article I am going to provide a brief example of the pivot function.
First of all let us set some data:
CREATE TABLE SAMPLE_TRANSACTIONS ( TRANSACTION_ID NUMBER(8), COMPANY_NAME VARCHAR2(30), STATE VARCHAR2(2), TRANSACTION_AMOUNT NUMBER(10,2) ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 1 , 'PALERMO' , 'TX' , 300.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 2 , 'PALERMO' , 'CT' , 600.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 3 , 'PALERMO' , 'PA' , 900.669 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 4 , 'BOLOGNA' , 'TX' , 500.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 5 , 'BOLOGNA' , 'CT' , 1100.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 6 , 'BOLOGNA' , 'PA' , 100.669 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 7 , 'PARMA' , 'NY' , 1300.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 8 , 'PARMA' , 'NJ' , 1600.22 ); INSERT INTO SAMPLE_TRANSACTIONS VALUES ( 9 , 'PARMA' , 'CA' , 1900.669 );
Now let us view the data:
SELECT * FROM SAMPLE_TRANSACTIONS;
TRANSACTION_ID | COMPANY_NAME | STATE | TRANSACTION_AMOUNT |
---|---|---|---|
1 | PALERMO | TX | 300.22 |
2 | PALERMO | CT | 600.22 |
3 | PALERMO | PA | 900.67 |
4 | BOLOGNA | TX | 500.22 |
5 | BOLOGNA | CT | 1100.22 |
6 | BOLOGNA | PA | 100.67 |
7 | PARMA | NY | 1300.22 |
8 | PARMA | NJ | 1600.22 |
9 | PARMA | CA | 1900.67 |
Now let us create a report that provides the sum of the transactions by company at a state level:
Let us first execute this query:
SELECT * FROM ( SELECT COMPANY_NAME , STATE , TRANSACTION_AMOUNT FROM SAMPLE_TRANSACTIONS ) PIVOT ( SUM(TRANSACTION_AMOUNT) FOR COMPANY_NAME IN ('BOLOGNA' AS BOLOGNA , 'PARMA' AS PARMA, 'PALERMO' AS PALERMO) ) ORDER BY STATE;
Results:
STATE | BOLOGNA | PARMA | PALERMO |
---|---|---|---|
CA | {null} | 1900.67 | {null} |
CT | 1100.22 | {null} | 600.22 |
NJ | {null} | 1600.22 | {null} |
NY | {null} | 1300.22 | {null} |
PA | 100.67 | {null} | 900.67 |
TX | 500.22 | {null} | 300.22 |
Now let us create a report that provides the sum of the transactions by state at a company level:
Let us first execute this query:
SELECT * FROM ( SELECT COMPANY_NAME , STATE , TRANSACTION_AMOUNT FROM SAMPLE_TRANSACTIONS ) PIVOT ( SUM(TRANSACTION_AMOUNT) FOR STATE IN ('CT' CT , 'NY' NY, 'TX' TX) ) ORDER BY COMPANY_NAME;
Results
COMPANY_NAME | CT | NY | TX |
---|---|---|---|
BOLOGNA | 1100.22 | {null} | 500.22 |
PALERMO | 600.22 | {null} | 300.22 |
PARMA | {null} | 1300.22 | {null} |
As you can see above there are a lot of possibilities on how to leverage the pivot function. For further reading I recommend this link Oracle Pivot.
Really liked this one…