SQL coalesce function tips

Question:  I am trying to take a list of orders and create a summary that pre-calculates the total of all orders to date for each customer by month. I have data that looks like this


And I want to create output that looks like :

I'm not on 11g, so I cannot use the pivot operator, but I've heard that the SQL coalesce clause can be used for this purpose.

Answer:  Yes, coalesce may work, and you can move rows to columns in many ways:

1. Converting Oracle rows to columns with SUM and CASE

2. Oracle - Grouping Related Rows

3. Displaying multiple columns per row with cross join lag-over and sys_cinnect_by_path

4. Oracle pivot SQL Tips
   

Answer (By Oracle author Laurent Schneider):  (For more advanced SQL tips, see Laurent's book Advanced Oracle SQL Programming.).  When embedded inside SQL, the coalesce function returns the first non-null expression in the list.  The coalesce can be used to substitute a NULL value, much like the NVL clause:

select coalesce(mycol,'value if null') from mytab;
select nvl(mycol,'value if null')      from mytab;

However, the NVL function is Oracle specific, whereas coalesce in generic SQL.  Also,  coalesce runs faster than NVL, and coalesce is a SQL92 standard.

Laurent shows how this can be solved using the SQL coalesce clause:

select 
  coalesce(a,b,c,d,e,f) a,
  coalesce(b,c,d,e,f)   b,
  coalesce(c,d,e,f)     c,
  coalesce(d,e,f)       d,
  coalesce(e,f)         e,
  f
from
  t;

For a practical example of using the coalesce function in Oracle SQL, consider when you send a book to my delivery address, if none then you send it to my correspondence address, if none then in my main address:

select
  coalesce(delivery_addr, correspondence_addr, main_addr)
from
   author;

---