Hi Guru's,
Oracle version - 19c
Sample data:
with test as
(
select '1' as item, '11' as subitem, 102 as total, '01-JAN-22' as mnth, 18.22 as mon_1, null as mon_2, null as mon_3, null as mon_4 from dual
union all
select '1' as item, '11' as subitem, 102 as total, '01-FEB-22' as mnth, null as mon_1, 2.6 as mon_2, null as mon_3, null as mon_4 from dual
union all
select '1' as item, '11' as subitem, 102 as total, '01-MAR-22' as mnth, null as mon_1, null as mon_2, 8.90 as mon_3, null as mon_4 from dual
union all
select '1' as item, '11' as subitem, 102 as total, '01-JAN-22' as mnth, null as mon_1, null as mon_2, null as mon_3, 61.88 as mon_4 from dual
)
select * from test;
Expected output:
ITEM SUBITEM TOTAL MON_1 MON_2 MON_2 MON_4
1 11 102 18.22 2.6 8.9 61.88
My sample data is as ABOVE. I want the output in a single records as there is monthwise percentage data displayed in mon_1 to mon_4 columns.
I have came to know from documentation that we can't use subquery in the "for in" clause of the pivot function, this is relaxed for pivot xml clause.
As my mnth column is dynamic so I can't use the static values as below. Is there any other approach rather that pivot or dynamic.
for mnth in ('01-JAN-2022' AS M1, '01-FEB-2022' AS M2, '01-MAR-2022' AS M3, '01-APR-2022' AS M4)
Regards,
Sid