I have table in below format,

Table 1

Table 2

I would like to convert the table A and B in to below format using join/query. The data(all rows) from the table-2 should get added based of the Category and Month_Year.

CodePudding user response：

Principally a CROSS JOIN needed among the tables after Category and Month_Year columns are distinctly selected, and Outstanding column is added in the main query as zero for non-matching values, otherwise returning values of it such as

SELECT t2.Bank, t2.Category, t2.Month_Year, t2.Loan_Type, 
       NVL(t1.Outstanding,0) AS Outstanding
  FROM (SELECT *
          FROM (SELECT DISTINCT Category, Month_Year FROM table1) 
         CROSS JOIN table2) t2
  LEFT JOIN table1 t1
    ON t2.Category = t1.Category
   AND t2.Month_Year = t1.Month_Year
   AND t2.Loan_Type = t1.Loan_Type
 ORDER BY t2.Category, t2.Month_Year, t1.Outstanding NULLS LAST

Demo

CodePudding user response：

Create a list of all items needed and left join Table1. For example

select items.Bank, items.Category, items.Month_Year, items.Loan_Type, coalesce(t1.Outstanding, 0) Outstanding
from (
    select t2.Bank, t2.Loan_Type, my.Month_Year,  cat.Category
    from (select distinct Month_Year
          from Table1) my
    cross join (select distinct Category
          from Table1) cat
    cross join Table2 t2
    ) items
left join Table1 t1 on items.Bank = t1.Bank and items.Loan_Type = t1.Loan_Type and items.Month_Year = t1.Month_Year and items.Category = t1.Category;

If there exists a table Categories replace it instead of derived categories in the query. You may also wish to generate a set of Month_Year from prameters or use a calendar table.