Here is scenario:
Cust Act_cnt Act1 Act2 Act3 Act4 Act5 ....... Act15
A 2 A01 A02 ? ? ? ...............?
B 1 B01 ? ? ? ? ...............?
C 3 C01 C02 C03 ? ? ...............?
after transpose I would like to have result like:
Can I use recursive to transpose as acct_cnt is available? Please suggest an approach.
you should avoid recursion for stuff like that, it will result in huge spool usage.
There are two common solutions for:
select cust, act1 as accnt from tab where act1 is not null
select cust, act2 as accnt from tab where act2 is not null
select cust, act3 as accnt from tab where act3 is not null
or a cross join to a table with integers like:
when 1 then act1
when 2 then act2
when 3 then act3
end as accnt
from tab cross join
(select day_of_calendar as x from sys_calendar.calendar
where x between 1 and 15) as dt
I have a huge table in production when I am trying to do column to row transpose using above first method I am getting spool space error.
Can any one plz helpme to do to query tuning here.
Thank you in advance.