Make Column Data as Column Header whole year in Oracle

Question

Hi im working on a query on oracle with the sample data like this [

expected Result but the whole year will also display other month even if there's no data for that month

i've search the internet for this but cant find any related to my problem

here's what i have tried but i think im in a wrong way

select * from(
select fd.empid,fd.employeename,to_char(fd.payrolldate,'Month') as Month,fd.for_deduction15th,d.DEDUCTED15TH,fd.for_deduction30th,d.DEDUCTED30TH from 
VW_MTS_FOR_DEDUCTION FD
full outer join VW_MTS_DEDUCTED D
on FD.EMPID = D.EMPID
and FD.PAYROLLDATE = D.PAYROLLDATE
)
pivot(max(Month) for Month in ('March' ,'April','February' ))

Answer 1

Your PIVOT was close, but you need to use the "deduction" columns in your pivot. Below is a sample query with assumed data since the actual data in the tables was not shared.

--Setup of sample data
WITH
    VW_MTS_FOR_DEDUCTION (empid,
                          employeename,
                          payrolldate,
                          for_deduction15th,
                          for_deduction30th)
    AS
        (SELECT 233, 'HERNANDEZ, CECILIA ALVANO', DATE '2021-3-1', NULL, 2 FROM DUAL
         UNION ALL
         SELECT 233, 'HERNANDEZ, CECILIA ALVANO', DATE '2021-4-1', NULL, 1778 FROM DUAL
         UNION ALL
         SELECT 233, 'HERNANDEZ, CECILIA ALVANO', DATE '2021-4-1', 1, NULL FROM DUAL
         UNION ALL
         SELECT 40079, 'ALLASAS, DERRICK AMANTE', DATE '2021-4-1', NULL, 1 FROM DUAL),
    VW_MTS_DEDUCTED (empid,
                     payrolldate,
                     DEDUCTED15TH,
                     DEDUCTED30TH)
    AS
        (SELECT 233, DATE '2021-3-1', NULL, 1 FROM DUAL
         UNION ALL
         SELECT 233, DATE '2021-4-1', NULL, 1777 FROM DUAL
         UNION ALL
         SELECT 233, DATE '2021-4-1', 1, NULL FROM DUAL
         UNION ALL
         SELECT 40079, DATE '2021-4-1', NULL, 1 FROM DUAL)
  --Actual query
  SELECT *
    FROM (SELECT fd.empid,
                 fd.employeename,
                 TRIM (TO_CHAR (fd.payrolldate, 'Month'))     AS mon,
                 fd.for_deduction15th,
                 d.DEDUCTED15TH,
                 fd.for_deduction30th,
                 d.DEDUCTED30TH                               as
            FROM VW_MTS_FOR_DEDUCTION FD
                 LEFT JOIN VW_MTS_DEDUCTED D ON FD.EMPID = D.EMPID AND FD.PAYROLLDATE = D.PAYROLLDATE)
         PIVOT (MAX (for_deduction15th) AS for_deduction15th,
                MAX (DEDUCTED15TH) AS DEDUCTED15TH,
                MAX (for_deduction30th) AS for_deduction30th,
                MAX (DEDUCTED30TH) AS DEDUCTED30TH
               FOR mon
               IN ('March' AS march, 'April' AS april))
ORDER BY empid;

   EMPID                 EMPLOYEENAME    MARCH_FOR_DEDUCTION15TH    MARCH_DEDUCTED15TH    MARCH_FOR_DEDUCTION30TH    MARCH_DEDUCTED30TH    APRIL_FOR_DEDUCTION15TH    APRIL_DEDUCTED15TH    APRIL_FOR_DEDUCTION30TH    APRIL_DEDUCTED30TH
________ ____________________________ __________________________ _____________________ __________________________ _____________________ __________________________ _____________________ __________________________ _____________________
     233 HERNANDEZ, CECILIA ALVANO                                                                              2                     1                          1                     1                       1778                  1777
   40079 ALLASAS, DERRICK AMANTE                                                                                                                                                                                  1                     1