How to combine 12 tables with some different and some same fields

Question

Hi friends i am having problem in joining tables in oracle my tables are shown bellow

table1 looks like

 id    Name    Jan
 7001  Deven   22 
 7002  Clause  55 
 7004  Monish  11 
 7010  Dipesh  55
 7070  Dipika  100

table2 looks like

  id      Name       Feb  
  7001    Deven      12 
  7002    Clause     15 
  7003    Nimesh     20 
  7004    Monish     21 
  7005    Ritesh     22 

table 3 looks like

  id      Name       Mar  
  7001    Deven      122 
  7020    Chalse     155 
  7003    Nimesh     88 
  7033    Monica     217 
  7070    Dipika     180

like this i am having 12 tables from january to December hwo can i combine this and get out put like below:

  id      Name      Jan   Feb  Mar ...................... Dec
  7001    Deven     22    12   122
  7002    Clause    55    15   -   .......................-
  7003    Nimesh    -     20   88  .......................2
  7004    Monish    11    21   -   .......................-
  7005    Ritesh    -     22   -   .......................20
  7010    Dipesh    55    -    -   .......................-
  7020    Chalse    -     -    155 .......................-
  7033    Monica    -     -    217 .......................100
  7070    Dipika    100   -    -   .......................-

Answer 1

My approach below is to create a derived table that contains all ids and names, and then use that to join against each of your tables. Unlike UNION ALL, UNION removes the duplicates for us:

select a.id, a.name, t1.Jan, t2.Feb, t3.Mar
from (
    select id, name from table1 
    union
    select id, name from table2
    union
    select id, name from table3 
) a
left outer join table1 t1 on a.id = t1.id
left outer join table2 t2 on a.id = t2.id
left outer join table3 t3 on a.id = t3.id

Answer 2

You might consider making those all one table with columns: "id, Name, Month, Quantity" (and then even a year if you are going to span years).

Answer 3

I would go for a GROUP BY without join (probably the most efficient since there is a single pass on each table -- the GROUP BY clause will de-dupe):

select id, name, max(jan) jan, max(feb) feb, /*...*/ max(dec) dec
  from (select id, name, jan jan, null feb, /*...*/ null dec
           from table1
         union all
         select id, name, null jan, feb feb, /*...*/ null dec
           from table2
         union all
         /*...*/
         select id, name, null jan, null feb, /*...*/ dec dec 
           from table12)
 group by id, name