Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

How to transpose rows to columns in oracle.

  • 16 April 2014
  • Author: Anil
  • Number of views: 14205
  • 0 Comments

In some case we need to transpose the table data that means we need to change the rows value into columns, and then this blog post will be surely helpful.

Let’s say we have to find out the trend of expenses of the customer or say some account no or mobile no for some few months to find out his or her behavior such as we have a data in table ‘Pivot_Table’  as 

MobileNO TotalExpenses 	Date
1 3000 20140101
2 5000 20140101
3 7000 20140101
1 5000 20140201
3 9000 20140201
2 3000 20140201
1 1111 20140301
2 2222 20140301
3 3333 20140301

Now, in order to find out the behavior analysis for the certain months per MobileNO, we need the output such as

MobileNO JanExpenses	FebExpenses	MarchExpenses
1 3000 5000 1111
2 5000 3000 2222
3 7000 9000 3333

 This can be easily done by using the PIVOT function for Oracle version11.2

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

But if your oracle version is old than 11.2 then you can use DECODE function to obtain such solution:

DECODE is a function in Oracle and is used to provide if-then-else type of logic to SQL. It is not available in MySQL or SQL Server. The syntax for DECODE is:


SELECT DECODE ( "column_name", "search_value_1", "result_1",
["search_value_n", "result_n"],
{"default_result"} );

"search_value" is the value to search for, and "result" is the value that is displayed.

Here is my Query:


SELECT /*+parallel(t,4)*/
MobileNO,
MAX(DECODE(Date, 20140101, TotalExpenses)) AS JanExpenses,
MAX(DECODE(Date, 20140201, TotalExpenses)) AS FebExpenses,
MAX(DECODE(Date, 20140301, TotalExpenses)) AS MarchExpenses
FROM
Pivot_Table t
GROUP BY MobileNO
ORDER BY MobileNO;

Hope this post will be helpful.

Thanks,

Anil Maharjan

Print
Categories: Analysis Services
Tags:
Rate this article:
2.0
Anil

AnilAnil

Highly motivated Business Intelligence Engineer having leadership abilities and team work skills as well as the ability to accomplish tasks under minimal direction and supervision. Has more than 5 years of development & implementation experience in HealthCare Data Analytics and Telecommunication

Other posts by Anil

Full biography Contact author

Please login or register to post comments.