I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.
I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.
The entire sample data and structure is available here:
The table with column headers is DATA_HEADER.
The table with value is DATA_VALUE.
The column headers and values need to be pivoted in order.
Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id.
When app_id is passed in proc/funct, the expected view should be:
So basically, the headers change for each app_id, and the max number of column headers will be 20. So the number or name shall vary as in the table DATA_HEADER. The values are uniquely identified on the basis of pid.
The order of column headers would be as per the seq column in DATA_HEADER. Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.
P.S. Application at the end is Oracle apex from where the proc/function would be called.
Oracle Version: 12.1