We have never had the need to do a Pivot like call in DB2.
How to actually pivot amounts by month and not have duplicate rows.
Select Distinct
c.CL_ID as "Client ID",
c.FRST_NME || ' ' || c.LST_NME as "Client Name",
Date(c.BRTH_DT) as "Client DOB",
d.RSRC_ID as "Provider ID",
h.REC_NME as "Provider Name",
Case When x.CHECK_DT Between Date('2021-10-01') and Date('2021-10-31') and x.CHECK_AMOUNT Is Not Null Then x.CHECK_AMOUNT End as "Oct 2021",
Case When x.CHECK_DT Between Date('2021-11-01') and Date('2021-11-30') and x.CHECK_AMOUNT Is Not Null Then x.CHECK_AMOUNT End as "Nov 2021"
From TPAYMNT_DTL d
Inner Join TCLIENT c On d.CL_ID = c.CL_ID
Inner Join TPAYMNT_HDR h On d.PAYMNT_HDR_ID = h.PAYMNT_HDR_ID
Inner Join TCHECK x On x.PAYMNT_LINE_ID = d.PAYMNT_LINE_ID and x.PAYMNT_HDR_ID = h.PAYMNT_HDR_ID
--Left Outer Join TCHECK Oct On x.PAYMNT_HDR_ID = Oct.PAYMNT_HDR_ID and Oct.CHECK_DT Between Date('2021-10-01') and Date('2021-10-31')
Where d.PAYMNT_TYP = 83441 -- Adoption Subsidy
and d.PAYMNT_STATUS = 13972 -- Paid
and h.PAYMNT_STATUS = 13972
and x.CHECK_DT Between Date('2021-10-01') and Date('2022-09-30')
and d.CL_ID = 169952
With UR;
What I want is just one record with 571.48 in the each of the columns.
TIA