PL/SQL: Some helpful SQL queries -1

In this post I am just putting some helpful SQL queries which will be useful at some point in your SQL / PLSQL Programming life. Most of these are taken from the OTN Forum answers or from my project experiences.

#1. How to convert Rows into Columns. 

Source Data

Expected Output

Using Pivot:

 with t as  
 (select 1 id,'MONDAY' day,'10:00' start_time,'12:00' end_time from dual  
 union  
 select 1,'TUESDAY','11:00','12:00' from dual  
 union  
 select 1,'WEDNESDAY','09:00','12:00' from dual  
 union  
 select 2,'MONDAY','08:00','10:00' from dual  
 union  
 select 3,'TUESDAY','06:00','09:00' from dual)  
 select * from t  
 pivot   
 (  
   max(start_time) as start_time, max(end_time) as end_time  
   for day in ('MONDAY' "Mon",'TUESDAY' "Tue",'WEDNESDAY' "Wed")  
 )  
 order by 1;  

#2. How to convert Columns into Rows. 
Source Data

Expected output

For versions prior to 11g. If you want null values also, then remove the where clause.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select decode(val,1,column1,2,column2,3,column3,4,column4,column5) display_val  
 from  t  
 cross join (select level val from dual connect by level <= 5)  
 where decode(val,1,column1,2,column2,3,column3,4,column4,column5) is not null;  

For 11g or above - using PIVOT/UNPIVOT. If you don't want the null values, remove 'include nulls'.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select val disp_val   
 from t   
 unpivot include nulls (val for col in(column1, column2, column3,column4,column5));   

Refer:
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 
Pivot and Unpivot

#3. How to get the first 100 Numbers in a query.

 select rownum val  
 from dual  
 connect by rownum <= 100;  

#4. How to get all the dates between 2 dates.
I have used 01-Jan-2015 as the start date and 31-Jan-2015 as the end date.

 select (to_date('01-Jan-2015','DD-MON-YYYY') + level - 1) date_val  
 from dual  
 connect by level <= (to_date('31-Jan-2015','DD-MON-YYYY') - to_date('01-Jan-2015','DD-MON-YYYY') + 1);  

#5. How to remove duplicates from one column.
Source Data

Expected Output

Normally this is job of the client / front end tool. But some reason you want this output in the query itself, you can use the below query.

 with t as  
 (select 'ABC Company' company,'India' branch,'11111' contact from dual union all  
  select 'ABC Company','Australia','22222'  from dual union all  
  select 'ABC Company','America'  ,'33333'  from dual union all  
  select 'XYZ Company','Australia','000000' from dual union all  
  select 'XYZ Company','America'  ,'111111' from dual  
 )  
 select  (case when row_number() over (partition by company order by branch desc) = 1  
              then company  
         end) company  
        ,branch  
        ,contact  
 from t  

#6. How to get the cumulative total.
Source Data

Expected Output

The data has to be sorted with the ITEM_NO and then with the Date and the Cumulative sum for each Item. 

 with t as (  
  select 1 item_no,to_date('2015-10-30','YYYY-MM-DD') inv_date ,30  amt from dual union  
  select 2 item_no,to_date('2015-10-23','YYYY-MM-DD') inv_date ,100 amt from dual union  
  select 2 item_no,to_date('2014-10-23','YYYY-MM-DD') inv_date ,70  amt from dual union  
  select 1 item_no,to_date('2014-09-10','YYYY-MM-DD') inv_date ,50  amt from dual union  
  select 1 item_no,to_date('2013-08-17','YYYY-MM-DD') inv_date ,40  amt from dual  
 )  
 select item_no,to_char(inv_date, 'DD-Mon-YYYY') invoice_date,amt amount  
   ,sum(amt) over (partition by item_no order by item_no,inv_date) as cumulative_amount  
 from t  
 order by item_no, inv_date;  

Feel free to point out if anything is missing/wrong in this blog.