CODEWITHSUNDEEP
sqloracle-databasejoin
Ann Garcia
Ann Garcia

Reputation: 11

join two tables and pivot in oracle sql

I need to join two tables.

Table 1 contains the quarterly lists and contains the following columns:

Year, ID, ID2, Quarter(value can be 1, 2, 3, 4), Amount_Due_for_the_Quarter
2018, 001, 000, 3, $1.00
2018, 001, 000, 4, $2.000

Table 2 contains the monthly submission of list and contains the following columns:

Year, ID, ID2, Mo (value is from January[1] to December[12]), Amount_Due_ per_Month
2018, 001,000, 8, $5.00
2018, 001,000, 10, $6.00
2018, 001,000, 11, $7.00

these tables can joined using ID and ID2 and year. the 1st table may or may not have submission for all the quarters. the 2nd table may or may not have submission for all the months. 1st quarter corresponds to month1, month2, 2nd quarter for months 4 and 5, and so on.

After join, output should be:

Year, ID, ID2, Quarter, Amount Due for Qtr, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
2018, 000, 001, 3, $1, null, null, null, null, null, null, null, $5.00, null, null, null, null
2018, 000, 001, 4, $2, null, null, null, null, null, null, null, null, null, $6.00, $7.00, null




select
      a.qtr,
      b.id,
      b.id2,
      nvl(b.Amount_Due_ per_Month,0)
from  tbl1  a
      left join tbl2 b
        on a.year = b.year
        and a.id = b.id
        and a.id2 = b.id2
  where a.year = '&year'
        and a.id = '&id'
        and a.id2 = '&id2';

but gives me:

Year, ID, ID2, Quarter, Amount Due for Qtr, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
2018, 000, 001, 3, $1, null, null, null, null, null, null, null, $5.00, null, $6.00, $7.00, null
2018, 000, 001, 4, null, null, null, null, null, null, null, null, null, null, $6.00, $7.00, null

Upvotes: 1

Views: 1823

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

You have to join quarters and months properly, for instance with floor((mo-1)/3) + 1 = qtr, this way you assign Jan, Feb, Mar to quarter 1, Apr, May, Jun to 2 etc. Like here:

select * 
  from (
    select * from t1 
      join t2 using (year, id, id2) 
      where id = '001' and id2 = '000' and floor((mo-1)/3) + 1 = qtr)
  pivot (max(amt_mth) for mo in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))

dbfiddle demo

Upvotes: 1

Related Questions