Oracle

How to transpose rows to columns in oracle.

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

Date Range for SQL and Oracle.

Hello all,

This will be my memorable blog post for this year. Since, this is my last blog post for this year acc to Nepali year.We are celebrating happy new year(01/01/2070 B.S) day after tomorrow :). so, want to wish you all happy Nepali new year.

This blog is related to the Date Range. While working I need to generate the date range for Oracle side date range from start date to enddate(i.e. 2010-01-01 to 2012-01-01) . Though it’s easy for me in SQL side but in oracle side I have to do some research for this since I am not an Oracle guy.

I think there might be different alternative way for this but If you want to generate the date range from startdate to enddate, then you can simply run the script as below.

For SQL Date Range script:

--startdate: 2010-01-01 enddate :2012-01-01
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DateRange') 
DROP TABLE DateRange
GO
CREATE TABLE [dbo].[DateRange](
	DateRange DATE
)
GO
DECLARE @StartDate Date;
DECLARE @EndDate Date;
SET @StartDate='2010-01-01'
SET @EndDate='2012-01-01'
WHILE (@StartDate<@EndDate)
BEGIN
 INSERT INTO  DateRange
 VALUES(@StartDate)
 SELECT @StartDate = DATEADD(month,1,@StartDate)--for month wise increment
 --SELECT @StartDate = DATEADD(year,1,@StartDate)--for year wise 
-- SELECT @StartDate = DATEADD(day,1,@StartDate) --for day wise
END
GO
--SELECT * FROM DateRange

For Oracle Date Range Script:

DECLARE
   does_not_exist   EXCEPTION;
   PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE DateRange';
EXCEPTION
   WHEN does_not_exist
   THEN
      NULL;
END;
/      
  CREATE TABLE DateRange (
  DateRange DATE 
)   
/                      
DECLARE
    vblStartdate DATE;
    vblEnddate DATE;
BEGIN 
    vblStartdate := To_Date('2010-01-01','YYYY-MM-DD');
    vblEnddate := To_Date('2012-01-01','YYYY-MM-DD');

    WHILE (vblStartDate <vblEndDate) 
    LOOP
EXECUTE IMMEDIATE 'INSERT INTO DateRange SELECT '''||vblStartDate||''' FROM DUAL';
EXECUTE IMMEDIATE ('SELECT Add_Months('''||vblStartDate||''',1) FROM DUAL')INTO  vblStartDate;
    END LOOP;
END;
/   
--select * from DateRange

OUTPUT:

DateRange -- month wise increment from startdate to enddate
2010-01-01
2010-02-01
2010-03-01
2010-04-01
2010-05-01
..........
2011-12-01

I think this post will be helpful for some of us who is looking for such date range.

Once again Happy Nepali New YEAR 🙂 !!!

Thanks,

Anil Maharjan