Transpose Rows to Columns dynamically.
699691Oct 16 2009 — edited Oct 19 2009Currently, I have a Materialized View (MV) that uses a pre-defined set of data points (row) and transposes them using the DECODE function. This MV gets refreshed every 5 minutes using an Oracle scheduler.
Disadvantages with this approach:
1. This refresh job takes over 20-25 minutes to complete due to the number of data points (ranging from 90 – 380 rows that will be converted to columns) and possibly, the DECODE function itself.
2. Also, since MV's cannot be edited – I have to drop and recreate the MV, I lose the flexibility of adding new data points.
Now we have new requirement:. I need to transpose the data points dynamically, which immediately rules out creating a MV. So, is a dynamically created table an alternative? How do I refresh this table with new data? I need to create at least 10 such tables in my database.
Thank you for taking the time to read through my requirement.
Thanks in advance.
-Anil.