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